基于列值的SELECT计数

Question

我有一个类似的表

Date  | Name  | StateData |
------+-------+-----------+    
xxxxx | Tom   | OPENED    |
xxxxx | David | NULL      |
xxxxx | Tom   | NULL      |
xxxxx | Brand | CLOSED    |
xxxxx | Brand | NULL      |
xxxxx | Brand | OPENED    |

我想要达到的结果是像这个

Date | Name | OPENED | CLOSED | UNUSED |
-----+------+--------+--------+--------+
xxxxx| Tom  |   1    |    0   |    1   |
xxxxx| David|   0    |    0   |    1   |
xxxxx| Brand|   1    |    1   |    1   |

我试过这种

SELECT 
Name,
[OPENED] = COUNT(CASE WHEN StateData ='OPENED' THEN StateData END),
[CLOSED] = COUNT(CASE WHEN StateData ='CLOSED' THEN StateData END),
[UNUSED] = COUNT(CASE WHEN StateData IS NULL THEN StateData END)
FROM 
[dbo].[StateData]
GROUP BY
Name

结果是，我在Name列中至少没有重复的记录，但我可以通过简单的select count(*(清楚地看到，列中的计数是不对的。

首先，我在谷歌上搜索了一些样本，并进行了上面的SELECT。

Answer 1

我会使用SUM()。您的NULL:有问题

SELECT Name,
SUM(CASE WHEN StateData = 'OPENED' THEN 1 ELSE 0 END) as opened
SUM(CASE WHEN StateData = 'CLOSED' THEN 1 ELSE 0 END) as closed
SUM(CASE WHEN StateData IS NULL THEN 1 ELSE 0 END) as unused
FROM [dbo].[StateData]
GROUP BY Name;

您的unused将始终为零，因为COUNT(NULL)始终为零。

Answer 2

这是一个"简单"的枢轴。然而，就我个人而言，我更喜欢使用Cross Tab，而不是限制性的PIVOT运算符：

SELECT [Date],
[Name],
COUNT(CASE StateData WHEN 'OPENED' THEN 1 END) AS OPENED,
COUNT(CASE StateData WHEN 'CLOSED' THEN 1 END) AS CLOSED,
COUNT(CASE WHEN StateData IS NULL THEN 1 END) AS UNUSED
FROM YourTable
GROUP BY [Date],
[Name];

请注意，您标记的SQL Server版本(2008(现在完全不受支持。因此，强烈建议您尽快查看升级路径。