locked
PIVOT on Multiple Columns RRS feed

  • Question

  • Hi,

    Using SQL Server 2008 R2 Management Studio but working on SQL Server 2012 instance.  DDL and Sample Data below.

    CREATE TABLE Test ([Office] varchar(50), [Type] varchar(50), [Amount1] int, [Amount2] int, [Amount3] int) INSERT INTO Test ([Office], [Type], [Amount1], [Amount2], [Amount3]) VALUES ('001', 'A', 100, 0, 2), ('001', 'B', 250, 207.5, 6), ('001', 'C', 235, 172.05, 83), ('010', 'C', 235, 206.25, 16), ('020', 'A', 100, 0, 1), ('020', 'C', 235, 175, 6)

    I need to generate a pivoted resultset that looks like below.

    | 001 | 010 | 020 |

    Type | Amount1 | Amount2 | Amount3 | Amount1 | Amount2 | Amount3 | Amount1 | Amount2 | Amount3 |

    A | 100 | 0 | 2 | NULL | NULL | NULL | 100 | 0 | 1 |

    B | 250 | 207.5 | 6 | NULL | NULL | NULL | NULL | NULL | NULL |

    C | 235 | 172.05 | 83 | 235 | 206.25 | 16 | 235 | 175 | 6 |

    How would I go about doing this?  I don't have much experience with pivoting data, and I've tried following some examples I've found online but can't seem to find the right combination to organize the data how I need it.  Any help is greatly appreciated!

    Best Regards

    Brad

    Thursday, January 22, 2015 8:04 PM

Answers

  • Hi 2012S4,

    I've made some modification. In the dynamic sql, the columns after SELECT are ordered so that the output can be as you expected. Please see below.

    CREATE TABLE Test
     ([Office] VARCHAR(50), [Type] VARCHAR(50), [Amount1] FLOAT, [Amount2] FLOAT, [Amount3] FLOAT);
    
    INSERT INTO Test
      ([Office], [Type], [Amount1], [Amount2], [Amount3])
    VALUES
      ('001', 'A', 100, 0, 2),
      ('001', 'B', 250, 207.5, 6),
      ('001', 'C', 235, 172.05, 83),
      ('010', 'C', 235, 206.25, 16),
      ('020', 'A', 100, 0, 1),
      ('020', 'C', 235, 175, 6);
    
    
    --insert the unpivot result into a temp table
    SELECT type,office+'_'+amount AS amount ,value INTO #Temp FROM test
    UNPIVOT(value FOR amount in ([Amount1],[Amount2],[Amount3])) AS ut;
    --SELECT * FROM #Temp;
    
    --get the pivot columns
    DECLARE @Columns VARCHAR(MAX);
    
    SET @Columns = stuff((Select DISTINCT  ',' + QUOTENAME(amount) 
    from #Temp
    order by ',' + QUOTENAME(amount) 
    for xml path ('')
    ) , 1,  1, '');
    
    DECLARE @SqlStr NVARCHAR(MAX);
    SET @SqlStr = N'SELECT type,'+@Columns+' FROM #Temp
    PIVOT
    (MAX(value) FOR amount IN ('+@Columns+')) as t';
    --PRINT @SqlStr;
    
    EXEC sp_executesql @SqlStr;
    
    DROP TABLE #Temp;
    DROP TABLE Test;

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    • Edited by Eric__Zhang Tuesday, January 27, 2015 2:53 AM
    • Proposed as answer by Eric__Zhang Thursday, January 29, 2015 8:55 AM
    • Marked as answer by Eric__Zhang Saturday, January 31, 2015 7:33 AM
    Tuesday, January 27, 2015 2:52 AM

All replies

  • CREATE TABLE #Test
      ([Office] varchar(50), [Type] varchar(50), [Amount1] int, [Amount2] int, [Amount3] int)
    
    INSERT INTO #Test
      ([Office], [Type], [Amount1], [Amount2], [Amount3])
    VALUES
      ('001', 'A', 100, 0, 2),
      ('001', 'B', 250, 207.5, 6),
      ('001', 'C', 235, 172.05, 83),
      ('010', 'C', 235, 206.25, 16),
      ('020', 'A', 100, 0, 1),
      ('020', 'C', 235, 175, 6);
    
    Select 
    [Type]
    , Sum(Case when Office = '001' then Amount1 end ) as Amount1_001
    , Sum(Case when Office = '010' then Amount1 end ) as Amount1_010
    , Sum(Case when Office = '020' then Amount1 end ) as Amount1_020
    , Sum(Case when Office = '001' then Amount2 end ) as Amount2_001
    , Sum(Case when Office = '010' then Amount2 end ) as Amount2_010
    , Sum(Case when Office = '020' then Amount2 end ) as Amount2_020
    , Sum(Case when Office = '001' then Amount3 end ) as Amount3_001
    , Sum(Case when Office = '010' then Amount3 end ) as Amount3_010
    , Sum(Case when Office = '020' then Amount3 end ) as Amount3_020
    From #Test
    GROUP BY [Type]
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, January 22, 2015 8:13 PM
  • Hi 2012S4,

    To achieve the expected output you can use the Dynamic Pivot. Please reference the below code.

    CREATE TABLE Test
     ([Office] VARCHAR(50), [Type] VARCHAR(50), [Amount1] FLOAT, [Amount2] FLOAT, [Amount3] FLOAT);
    
    INSERT INTO Test
      ([Office], [Type], [Amount1], [Amount2], [Amount3])
    VALUES
      ('001', 'A', 100, 0, 2),
      ('001', 'B', 250, 207.5, 6),
      ('001', 'C', 235, 172.05, 83),
      ('010', 'C', 235, 206.25, 16),
      ('020', 'A', 100, 0, 1),
      ('020', 'C', 235, 175, 6);
    
    
    --insert the unpivot result into a temp table
    SELECT type,office+'_'+amount AS amount ,value INTO #Temp FROM test
    UNPIVOT(value FOR amount in ([Amount1],[Amount2],[Amount3])) AS ut;
    --SELECT * FROM #Temp;
    
    --get the pivot columns
    DECLARE @Columns VARCHAR(MAX);
    SELECT @Columns = ISNULL(@Columns+',','')+QUOTENAME(amount) FROM (SELECT DISTINCT amount FROM #Temp) AS T;
    
    DECLARE @SqlStr NVARCHAR(MAX);
    SET @SqlStr = N'SELECT * FROM #Temp
    PIVOT
    (MAX(value) FOR amount IN ('+@Columns+')) as t';
    --PRINT @SqlStr;
    
    EXEC sp_executesql @SqlStr;
    
    DROP TABLE #Temp;
    DROP TABLE Test;



    If you have any feedback on our support, please click here.


    Eric Zhang
    TechNet Community Support


    Friday, January 23, 2015 2:10 AM
  • One more method

    SELECT Type,

           MAX([001]) AS Amount1_001,
           MAX([010]) AS Amount1_010,
           MAX([020]) AS  Amount1_020,
      MAX([0012000]) AS Amount2_001,
           MAX([0102000]) AS Amount2_010,
           MAX([0202000]) AS Amount2_020,
      MAX([0013000]) AS Amount3_001,
           MAX([0103000]) AS Amount3_010,
           MAX([0203000]) AS Amount3_020

    FROM
    (SELECT [Office], [Type], 

                 [Office] + '2000' AS [Office1],
    [Office] + '3000' AS [Office2],
    MAX([Amount1]) AS [VALAmount1], 
    MAX([Amount2]) AS [VALAmount2],
    MAX([Amount3]) AS [VALAmount3]

          FROM Test
    GROUP BY [Office], [Type]
    ) AS T
    PIVOT

         (MAX([VALAmount1]) FOR Office IN 

         ([001],[010],[020])) AS P1

          PIVOT

         (MAX([VALAmount2]) FOR Office1 IN 

         ([0012000],[0102000],[0202000])) AS P2

     PIVOT

     (MAX([VALAmount3]) FOR Office2 IN 

         ([0013000],[0103000],[0203000])) AS P3

    GROUP BY Type;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, January 23, 2015 5:39 AM
    Answerer
  • Hi Eric,

    I've tried your solution and it seems to work - almost.  The pivoted output as shown in your example shows the data correctly, but in the wrong order.  I'd want all the 001 columns first, then 010, then 020, etc.  Any ideas?

    Thanks!

    Monday, January 26, 2015 3:38 PM
  • Can you elaborate on your follow-up?  Eric's results look identical to what you have above?  And it has all the 001 columns first, etc.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, January 26, 2015 3:48 PM
  • Try this:

    CREATE TABLE Test
     ([Office] VARCHAR(50), [Type] VARCHAR(50), [Amount1] FLOAT, [Amount2] FLOAT, [Amount3] FLOAT);
    
    INSERT INTO Test
      ([Office], [Type], [Amount1], [Amount2], [Amount3])
    VALUES
      ('001', 'A', 100, 0, 2),
      ('001', 'B', 250, 207.5, 6),
      ('001', 'C', 235, 172.05, 83),
      ('010', 'C', 235, 206.25, 16),
      ('020', 'A', 100, 0, 1),
      ('020', 'C', 235, 175, 6);
    
    
    --insert the unpivot result into a temp table
    SELECT type,office+'_'+amount AS amount ,value INTO #Temp FROM test
    UNPIVOT(value FOR amount in ([Amount1],[Amount2],[Amount3])) AS ut;
    --SELECT * FROM #Temp;
    
    --get the pivot columns
    DECLARE @Columns VARCHAR(MAX);
    
    SET @Columns = stuff((Select DISTINCT  ',' + QUOTENAME(amount) 
    from #Temp
    order by ',' + QUOTENAME(amount) 
    for xml path ('')
    
    ) , 1,  1, '');
    
    
    
    DECLARE @SqlStr NVARCHAR(MAX);
    SET @SqlStr = N'SELECT * FROM #Temp
    PIVOT
    (MAX(value) FOR amount IN ('+@Columns+')) as t';
    --PRINT @SqlStr;
    
    EXEC sp_executesql @SqlStr;
    
    DROP TABLE #Temp;
    DROP TABLE Test;


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, January 26, 2015 3:58 PM
  • Hi 2012S4,

    I've made some modification. In the dynamic sql, the columns after SELECT are ordered so that the output can be as you expected. Please see below.

    CREATE TABLE Test
     ([Office] VARCHAR(50), [Type] VARCHAR(50), [Amount1] FLOAT, [Amount2] FLOAT, [Amount3] FLOAT);
    
    INSERT INTO Test
      ([Office], [Type], [Amount1], [Amount2], [Amount3])
    VALUES
      ('001', 'A', 100, 0, 2),
      ('001', 'B', 250, 207.5, 6),
      ('001', 'C', 235, 172.05, 83),
      ('010', 'C', 235, 206.25, 16),
      ('020', 'A', 100, 0, 1),
      ('020', 'C', 235, 175, 6);
    
    
    --insert the unpivot result into a temp table
    SELECT type,office+'_'+amount AS amount ,value INTO #Temp FROM test
    UNPIVOT(value FOR amount in ([Amount1],[Amount2],[Amount3])) AS ut;
    --SELECT * FROM #Temp;
    
    --get the pivot columns
    DECLARE @Columns VARCHAR(MAX);
    
    SET @Columns = stuff((Select DISTINCT  ',' + QUOTENAME(amount) 
    from #Temp
    order by ',' + QUOTENAME(amount) 
    for xml path ('')
    ) , 1,  1, '');
    
    DECLARE @SqlStr NVARCHAR(MAX);
    SET @SqlStr = N'SELECT type,'+@Columns+' FROM #Temp
    PIVOT
    (MAX(value) FOR amount IN ('+@Columns+')) as t';
    --PRINT @SqlStr;
    
    EXEC sp_executesql @SqlStr;
    
    DROP TABLE #Temp;
    DROP TABLE Test;

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support

    • Edited by Eric__Zhang Tuesday, January 27, 2015 2:53 AM
    • Proposed as answer by Eric__Zhang Thursday, January 29, 2015 8:55 AM
    • Marked as answer by Eric__Zhang Saturday, January 31, 2015 7:33 AM
    Tuesday, January 27, 2015 2:52 AM