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?

Answer 1

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).