Pivot multiple columns over one column in SQL Server

Question

I have a temp table named #rdata like this :

Months      KPI_1       KP1_2        KPI_3   
-------------------------------------------
Jan-18       x            x            x 
Feb-18       x            x            x 
Mar-18       x            x            x 
Apr-18       x            x            x 
Aug-18       x            x            x

I want to change it to this table:

 KPIs      Jan-18     Feb-18      Mar-18 ....
 -------------------------------------------
 KPI_1       x            x            x 
 KPI_2       x            x            x 
 KPI_3       x            x            x

I have come this far:

 ----Creating a list of Months  

    SELECT @Columns =   COALESCE(@Columns + ', ','') + QUOTENAME(Months)
     FROM
    (SELECT DISTINCT Months
      FROM  #rdata ) AS B



  SET @SQL = 'SELECT  ' + @Columns + ' KPI_1, KPI_2,KPI_3 FROM #rdata
  ) as PivotData

  PIVOT
  (
    FOR Months IN (' + @Columns + ')
   ) AS PivotResult'
   EXEC(@SQL)

It doesn't seem to work, any idea what am I missing?

Do you get an error message? What does the SQL look like when printed out? — Nick.Mc
– Nick.Mc, Commented Dec 7, 2018 at 5:31
...also it's a good idea to think twice about pivoting in a database. Pivoting is usually for presentation purposes, and is better handled in the presentation layer. Personally I would not use PIVOT for this. Instead I would use a cross join to get KPI's on the rows and CASE WHEN / GROUP BY to pivot to columns — Nick.Mc
– Nick.Mc, Commented Dec 7, 2018 at 5:33
@Nick.McDermaid.. FYI The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. From microsoft.com — Pugal
– Pugal, Commented Dec 7, 2018 at 8:14

gotqn · Accepted Answer · 2018-12-07 05:48:57Z

2

Just perform UNPIVOT, then PIVOT. Here is full working example:

DROP TABLE IF EXISTS #rdata;

CREATE TABLE #rdata
(
    [Months] VARCHAR(12)
   ,[KPI_1] INT
   ,[KPI_2] INT
   ,[KPI_3] INT
);

INSERT INTO #rdata ([Months], [KPI_1], [KPI_2], [KPI_3])
VALUES ('Jan-18 ', 1, 2, 3)
      ,('Feb-18 ', 4, 5, 6)
      ,('Mar-18 ', 7, 8, 9)
      ,('Apr-18 ', 10, 11, 12)
      ,('Aug-18 ', 13, 14, 15)


DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX);


SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',[' + CAST([Months] AS VARCHAR(12)) + ']'
                                FROM #rdata
                                GROUP BY [Months]
                                ORDER BY [Months]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynammicTSQLStatement = N'
SELECT *
FROM #rdata
UNPIVOT
(
    [value] FOR [KPIs] IN ([KPI_1], [KPI_2], [KPI_3])
) UNPVT
PIVOT
(
    MAX([value]) FOR [Months] IN (' + @DynamicPIVOTColumns + ')
) PVT'


EXEC sp_executesql @DynammicTSQLStatement;

Note, that when I am creating the dynamic columns based on the month column, I am ordering by the month value itself. If you want to have/sort the months chronologically, you need to store the data in different format (like 2018-01, 2017-10) for sort by other column (like primary key or something like that).

answered Dec 7, 2018 at 5:48

gotqn

44k47 gold badges168 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ariox66 Over a year ago

Do the KPIs have to be all the same type? one is int, the others are decimal. I get this error: conflicts with the type of other columns specified in the UNPIVOT list

gotqn Over a year ago

Yes, you need to cast them, it easier to case them to strings.

Collectives™ on Stack Overflow

Pivot multiple columns over one column in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related