Thursday, July 8, 2010

SQL Server : Transform Columns into Rows

Suppose, we have the table “tblSample” with the following data.
SeqMonthNew Policy CountNew PremiumClaims RecordedClaims CostLoss Ratio
12010/06/011000100000.9566666.9866.66
22010/05/011100200000.91055555.9827.77
32010/04/011200300000.91544444.9814.81
42010/03/011300400000.92033333.988.33
52010/02/011400500000.92522222.984.44
62010/01/011500600000.93011111.981.85


The query to transform the Columns of this table to Rows:

SELECT   Seq
        ,ROW_NUMBER() OVER(PARTITION BY [Month] ORDER BY Seq) AS 'RowNumber'
        ,CONVERT(VARCHAR, [Month], 111) AS [Month], Title, aValue
INTO #temp
FROM
     (SELECT  Seq 
             ,[Month]
             ,[New Policy Count]
             ,[New Premium]          
             ,[Claims Recorded]    

             ,[Claims Cost] 
             ,[Loss Ratio]
      FROM tblSample) p
      UNPIVOT      
     (aValue FOR  Title IN ([New Policy Count],[New Premium],[Claims Recorded],[Claims Cost],[Loss Ratio]))
AS unpvt

DECLARE @SQLQuery     VARCHAR(MAX)
       ,@PivotColumns VARCHAR(MAX)

SET    @PivotColumns = ''
SELECT @PivotColumns = @PivotColumns+ '['+ [Month] +'],' FROM (SELECT
DISTINCT [Month] FROM #TEMP) a
SET    @PivotColumns = SUBSTRING(@PivotColumns, 1,LEN(@PivotColumns) - 1)

SET @SQLQuery = 'SELECT Title,' + @PivotColumns +
             'FROM (SELECT [Month], RowNumber, Title, aValue FROM #temp) s
              PIVOT (SUM(aValue) FOR [Month] IN ('
+ @PivotColumns + ')) p
              ORDER BY RowNumber'
EXEC (@SQLQuery)
DROP TABLE #temp
The final output looks like:
Title2010/01/012010/02/012010/03/012010/04/012010/05/012010/06/01
New Policy Count150014001300120011001000
New Premium600000.9500000.9400000.9300000.9200000.9100000.9
Claims Recorded30252015105
Claims Cost11111.9822222.9833333.9844444.9855555.9866666.98
Loss Ratio1.854.448.3314.8127.7766.66