如何透视按表分组 [英] 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

我已经完成了这个查询

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

我可以这样做

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 个类.

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

推荐答案

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

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 如下:

    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

这篇关于如何透视按表分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！