CODEWITHSUNDEEP
sqlsql-serversql-server-2012pivot
DooDoo
DooDoo

Reputation: 13467

Pivot Return null based on a column values

Please consider this Table:

Id     Year     Month      Type      Value
------------------------------------------
1      2011      1          T1       1000
2      2012      1          T1       2000
3      2011      1          T2       5000
4      2012      1          T2       500
5      2011      1          T3       11000
6      2012      1          T3       800

I want to create this result :

Id     Year     Month      T1      T2      T3
------------------------------------------------
1      2011      1         1000    5000    11000
2      2012      1         2000    500     800

I want to use PIVOT on TYPE column based on it's value and I wrote this script:

SELECT [Year],[Month], [T1],[T2],[T3]
FROM (SELECT  [Year],[Month],Value,[Type]
      FROM MyTable 
     ) p 
PIVOT (MAX(VALUE) FOR [Type] IN ([[T1],[T2],[T3])) AS pvt

but this script generate 6 rows like this:

Id     Year     Month      T1      T2      T3
------------------------------------------------
1      2011      1         1000    Null    Null
2      2012      1         2000    Null    Null    
3      2011      1         Null    5000    Null
4      2012      1         Null    500     Null 
5      2011      1         Null    Null    11000
6      2012      1         Null    Null    800

How I can merge this result?

thanks

Upvotes: 0

Views: 56

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can try this:

DECLARE @t TABLE
    (
      ID INT ,
      Year INT ,
      Month INT ,
      Type CHAR(2) ,
      Value INT
    )

INSERT  INTO @t
VALUES  ( 1, 2011, 1, 'T1', 1000 ),
        ( 2, 2012, 1, 'T1', 2000 ),
        ( 3, 2011, 1, 'T2', 5000 ),
        ( 4, 2012, 1, 'T2', 500 ),
        ( 5, 2011, 1, 'T3', 11000 ),
        ( 6, 2012, 1, 'T3', 800 );
WITH    cte
          AS ( SELECT   t1.Year ,
                        t1.Month ,
                        t1.Value AS T1 ,
                        t2.Value AS T2 ,
                        t3.Value AS T3 ,
                        ROW_NUMBER() OVER ( PARTITION BY t1.Year, t1.Month ORDER BY t1.ID, t2.ID, t3.ID ) AS rn ,
                        DENSE_RANK() OVER ( ORDER BY t1.ID ) AS ID
               FROM     @t t1
                        JOIN @t t2 ON t1.Type <> t2.Type
                        JOIN @t t3 ON t1.Type <> t3.Type AND t2.Type <> t3.Type
               WHERE    t1.Year = t2.Year
                        AND t1.Year = t3.Year
                        AND t1.Month = t2.Month
                        AND t1.Month = t2.Month
             )
    SELECT  ID, Year, Month, T1, T2, T3
    FROM    cte
    WHERE   rn = 1

Output:

ID  Year    Month   T1    T2    T3
1   2011    1       1000  5000  11000
2   2012    1       2000  500   800

But your code does the same, because Year and Month will be in rows by eliminating, Type becomes spreading element and Value is aggregation element.

I suspect that you have ID in your table expression:

FROM (SELECT  [Year],[Month],Value,[Type],[ID]
      FROM MyTable 
     ) p

In this case grouping element becomes Year, Month and ID by eliminating !!! Thats why you are getting all 6 rows.

DECLARE @t TABLE
    (
      ID INT ,
      Year INT ,
      Month INT ,
      Type CHAR(2) ,
      Value INT
    )

INSERT  INTO @t
VALUES  ( 1, 2011, 1, 'T1', 1000 ),
        ( 2, 2012, 1, 'T1', 2000 ),
        ( 3, 2011, 1, 'T2', 5000 ),
        ( 4, 2012, 1, 'T2', 500 ),
        ( 5, 2011, 1, 'T3', 11000 ),
        ( 6, 2012, 1, 'T3', 800 );

SELECT [Year],[Month], [T1],[T2],[T3]
FROM (SELECT  [Year],[Month],Value,[Type]
      FROM @t 
     ) p 
PIVOT (MAX(VALUE) FOR [Type] IN ([T1],[T2],[T3])) AS pvt

Output:

Year    Month   T1    T2    T3
2011    1       1000  5000  11000
2012    1       2000  500   800

Upvotes: 2

Related Questions