locked
Querying a table to get desired format RRS feed

  • Question

  • User2084211446 posted
    S1         W1         100
    S2         W1         10
    S2         W2         50
    S3         W2         20

    How can I display the data from above table in the format below using T-sql query?

    Column1         W1   W2  //  column names

    S1                   100    0
    S2                   10     50
    S3                    0      20

    ;with cte(Column1, W1) as (select Column1, Quantity from table1 where Column2 = 'w1'),
    cte1(Column1, W2) as (select Column1 Quantity from table1 where Column2  = 'w2')

    Select c1.Column1, c.W1, c1.w2 from Cte as c full outer join cte1 as c1 on c.Column1 = c1.Column1

    My query gives null value either for S1 or S3. 

    Any suggesstions how I can approach this problem

    Thanks,

    Rose

    Sunday, December 11, 2016 2:02 AM

Answers

  • User-967720686 posted

    Hi, 

    Please run the statements below in query analyzer

    Declare  @Table Table (col1 varchar(10), col2 varchar(10), col3 varchar(10))
    
    Insert Into @Table 
    Select 'S1', 'W1', '100' Union
    Select 'S2', 'W1', '10'  Union
    Select 'S2', 'W2', '50'  Union
    Select 'S3', 'W2', '20'
    
    
    Select	col1, ISNULL([w1], 0) As [W1], ISNULL([w2], 0) As [W2]
    From	@Table 
    	Pivot (Max(col3) For Col2 In ([W1], [W2])) As pv



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 11, 2016 11:51 PM

All replies

  • User-967720686 posted

    Hi, 

    Please run the statements below in query analyzer

    Declare  @Table Table (col1 varchar(10), col2 varchar(10), col3 varchar(10))
    
    Insert Into @Table 
    Select 'S1', 'W1', '100' Union
    Select 'S2', 'W1', '10'  Union
    Select 'S2', 'W2', '50'  Union
    Select 'S3', 'W2', '20'
    
    
    Select	col1, ISNULL([w1], 0) As [W1], ISNULL([w2], 0) As [W2]
    From	@Table 
    	Pivot (Max(col3) For Col2 In ([W1], [W2])) As pv



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 11, 2016 11:51 PM
  • User2084211446 posted

    Thanks a lot Farhan!

    Regards,

    Rose

    Monday, December 12, 2016 12:51 AM