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

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"

Answer 2

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 3

It might be easiest to get the data as you have and convert it using linq. You can never really get out an object with properties of Type1, Type2 etc because you need to do this at runtime but you can get an array of the values. You would first need to get a list of all rating types and then have a second query to pivot the data. Something like this:

public class SomeClass
{
    public DateTime RatingDate;
    public string RatingType;
    public int Rating;
}

var data = new SomeClass[]
{
    new SomeClass() { RatingDate = DateTime.Parse("6/7/2012"), RatingType = "Type1", Rating = 1 },
    new SomeClass() { RatingDate = DateTime.Parse("6/7/2012"), RatingType = "Type2", Rating = 3 },
    new SomeClass() { RatingDate = DateTime.Parse("6/7/2012"), RatingType = "Type3", Rating = 5 },
    new SomeClass() { RatingDate = DateTime.Parse("8/7/2012"), RatingType = "Type1", Rating = 5 },
    new SomeClass() { RatingDate = DateTime.Parse("8/7/2012"), RatingType = "Type2", Rating = 2 },
    new SomeClass() { RatingDate = DateTime.Parse("8/7/2012"), RatingType = "Type3", Rating = 4 },
    new SomeClass() { RatingDate = DateTime.Parse("8/7/2012"), RatingType = "Type4", Rating = 1 }
};

var ratingTypes = data.Select(i => i.RatingType)
    .Distinct().OrderBy(i => i).ToArray();

var results = data.GroupBy(i => i.RatingDate)
    .Select(g => new { RatingDate = g.Key, Ratings = ratingTypes.GroupJoin(g, o => o, i => i.RatingType, (o, i) => i.Select(x => x.Rating).Sum()).ToArray() })
    .ToArray();

You could break the group join out into a separate function to make it a bit more readable if you like:

Func<IEnumerable<SomeClass>, int[]> func
    = group => ratingTypes.GroupJoin(group, o => o, i => i.RatingType, (o, i) => i.Select(x => x.Rating).Sum()).ToArray();

var results = data.GroupBy(i => i.RatingDate)
    .Select(g => new { RatingDate = g.Key, Ratings = func(g) })
    .ToArray();