如何将多语句计数查询转换为单行列数透视表？

方法一：使用CASE表达式（通用所有数据库） #

这种方式兼容性最强，几乎所有SQL数据库都支持，通过条件判断统计各状态的记录数：

SELECT
    COUNT(*) AS Total,
    COUNT(CASE WHEN [Status] = 1 THEN 1 END) AS New,
    COUNT(CASE WHEN [Status] = 2 THEN 1 END) AS Active,
    COUNT(CASE WHEN [Status] = 3 THEN 1 END) AS Closed
FROM @results;

原理：COUNT()函数会自动忽略NULL值，当Status不匹配时CASE返回NULL，不会被计入统计，刚好符合需求。

方法二：使用PIVOT运算符（适用于SQL Server等支持的数据库） #

如果是SQL Server环境，用PIVOT语法更简洁：

SELECT
    (SELECT COUNT(*) FROM @results) AS Total,
    [1] AS New,
    [2] AS Active,
    [3] AS Closed
FROM (
    SELECT [Status] FROM @results
) AS SourceTable
PIVOT (
    COUNT([Status])
    FOR [Status] IN ([1], [2], [3])
) AS PivotTable;

也可以在子查询里先计算总记录数，再一起透视：

SELECT
    Total,
    [1] AS New,
    [2] AS Active,
    [3] AS Closed
FROM (
    SELECT
        [Status],
        COUNT(*) OVER () AS Total
    FROM @results
) AS SourceTable
PIVOT (
    COUNT([Status])
    FOR [Status] IN ([1], [2], [3])
) AS PivotTable;

内容的提问来源于stack exchange，提问作者Ivan Debono