sql-server - 如何透视按表分组

Question

我有一张看起来像这样的桌子

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

我已经通过了这个查询

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

我想让表格看起来像这样

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

我可以这样做

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

但是 myClass 可以有新的类，并且不限于 3 个类，它可以增长到 200 个类。

标签： sql-serverpivot

Answer 1

这是一个generic将生成Dynamic sql语句并按字段返回SUM记录PARTITIONED的解决方案GROUP BY

SQL如下：

    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