CODEWITHSUNDEEP
sql-serverpivot
asmgx
asmgx

Reputation: 8014

How to Pivot a grouped by table

I have a table that looks like this

myDate    myClass    myCount
1 Jan 17  A1         70
1 Jan 17  B2         60
1 Jan 17  C3         90
2 Jan 17  A1         50
2 Jan 17  B2         80
3 Jan 17  A1         20
3 Jan 17  C3         10

that I have got through this query

SELECT myDate, myClass, Count(*)
FROM myTable
GROUP BY myDate, myClass

I want to Pivot the table to look like this

myDate    A1   B2   C3
1 Jan 17  70   60   90
2 Jan 17  50   80   0
3 Jan 17  20   0    10

I can do it by something like this

SELECT myDate, 
SUM(CASE myClass WHEN 'A1' THEN 1 ELSE 0 END) AS A1,
SUM(CASE myClass WHEN 'B2' THEN 1 ELSE 0 END) AS B2,
SUM(CASE myClass WHEN 'C3' THEN 1 ELSE 0 END) AS C3
FROM myTable
GROUP BY myDate

but myClass could have new Classes and not limited to 3 classes only it can grow to 200 classes.

Upvotes: 0

Views: 49

Answers (1)

Jonathan Larouche
Jonathan Larouche

Reputation: 992

Here is a generic solution that will generate a Dynamic sql statement and will return the SUM of records PARTITIONED by GROUP BY Fields

SQL goes as follow:

    DECLARE @SQL NVARCHAR(MAX) = CONCAT (
        N'SELECT myDate, ',
        (
            SELECT STUFF((
                SELECT CONCAT (
                        N',SUM(CASE WHEN [myClass] = ''',
                        myClass,
                        N''' THEN myCount ELSE 0 END) AS [',
                        myClass,
                        N']'
                        )
                FROM myTable
                GROUP BY myClass
                FOR XML PATH('')
                ), 1, 1, '')
            ),
        N'FROM myTable GROUP BY myDate'
        )

EXEC sp_executesql @SQL

Upvotes: 1

Related Questions