How to pivot a normal sql query with dynamic columns

Question

I have a Sql query which returns something like below:

Rating as at    Rating type    Rating
--------------------------------------
6/7/2012        Type1          A
6/7/2012        Type2          A+
6/7/2012        Type3          B
8/7/2012        Type1          C
8/7/2012        Type2          C+
8/7/2012        Type3          B
8/7/2012        Type4          A

As you can see the rating type is dynamic and I would like to display it in a pivot but I really do not know how to achieve this. The end result I would like is something like below:

Rating as at    Type1   Type2   Type3   Type4
6/7/2012        A       A+       B
8/7/2012        C       C       C+       A

I want to know how I can achieve this using sql. Or best how I would do it using LINQ C#??

Help would be appreciated. Thanks.

Answer 1

Updated answer based on OP's comment below - This update will now convert results to letter grades

The following uses a combination of SQL Server's Pivot operator (MSDN) and well as the EXEC statement (MSDN).

The T-SQL solution will handle completely dynamic columns.

--Create a temp table to hold values
CREATE TABLE #MyTable (
[Rating as at] DATE,
[Rating type] Nvarchar(30),
Rating FLOAT )

INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('6/7/2012','Type1', 1)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('6/7/2012','Type2', 3)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('6/7/2012','Type3', 5)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('8/7/2012','Type1', 5)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('8/7/2012','Type2', 2)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('8/7/2012','Type3', 4)
INSERT INTO #MyTable ([Rating as at],[Rating type],Rating) VALUES ('8/7/2012','Type4', 1)
SELECT DISTINCT [Rating type] INTO #MyTableDistinct FROM #MyTable

--Create a string of dynamic fields
DECLARE @MyVar1 nvarchar(max),@MyVar2 nvarchar(max)
SELECT @MyVar1 = COALESCE(@MyVar1,'') + '[' + [Rating type] + ']' + ', '  FROM #MyTableDistinct 
SELECT @MyVar1 = LEFT(@MyVar1, LEN(@MyVar1) - 1)

--Create a string of dynamic CASE statements to be used to convert pivoted results to letter grades
--Update the CASE steatement to handle different grade types
SELECT @MyVar2 = COALESCE(@MyVar2,'') + 'CASE WHEN [' + [Rating type] + '] IS NULL THEN ''N/A'' WHEN [' + [Rating type] + '] > 4 THEN ''A'' WHEN [' + [Rating type] + '] > 3 THEN ''B'' WHEN [' + [Rating type] + '] > 2 THEN ''C'' ELSE ''F'' END AS [' + [Rating type] + '], '  FROM #MyTableDistinct 
SELECT @MyVar2 = LEFT(@MyVar2, LEN(@MyVar2) - 1)

--Build a SQL string to be later execute
--This is where all of the PIVOT magic happens
DECLARE @MySQLStatement nvarchar(max)
SET @MySQLStatement = 'SELECT [Rating as at],' + @MyVar1 +  ' INTO #MyPivotTable FROM 
    (SELECT [Rating as at],[Rating type],Rating from #MyTable) AS p1
    PIVOT (
        avg(Rating) FOR [Rating type] IN (' + @MyVar1 + ') 
    ) as p2;SELECT [Rating as at], ' + @MyVar2 + ' FROM #MyPivotTable;DROP TABLE #MyPivotTable;'

--Execute the SQL string
EXEC(@MySQLStatement)


DROP TABLE #MyTableDistinct
DROP TABLE #MyTable

Answer 2

select "Rating as at", 
max(case when Rating_Type = 'Type1' then Rating else 0 end) as Type1,
max(case when Rating_Type = 'Type2' then Rating else 0 end) as Type2,
max(case when Rating_Type = 'Type3' then Rating else 0 end) as Type3,
max(case when Rating_Type = 'Type4' then Rating else 0 end) as Type4,
from Table
group by "Rating as at"