SQL Server行列转换求助：将统计结果从列转成行

嘿，这事儿好办！要把你当前的横向统计结果转成纵向的键值对格式，在SQL Server里有两种常用方法，我都给你列出来：

方法1：用UNION ALL拆分统计 #

这种写法直观易懂，分别对每个指标做统计后合并结果：

SELECT 'Categories' AS [#Object#], COUNT(DISTINCT Category) AS [#Count#] FROM Associate
UNION ALL
SELECT 'Orders' AS [#Object#], COUNT(DISTINCT [Order Number]) AS [#Count#] FROM Associate
UNION ALL
SELECT 'Products' AS [#Object#], COUNT(DISTINCT Product) AS [#Count#] FROM Associate

方法2：用CROSS APPLY + VALUES（更高效） #

如果你的Associate表数据量比较大，推荐用这种方法——它只需要扫描一次表完成所有统计，再把列转成行，性能更好：

SELECT obj.[#Object#], obj.[#Count#]
FROM (
    -- 先一次性算出所有统计值
    SELECT 
        COUNT(DISTINCT Category) AS Categories,
        COUNT(DISTINCT [Order Number]) AS Orders,
        COUNT(DISTINCT Product) AS Products
    FROM Associate
) AS src
-- 把横向的列转换成纵向的行
CROSS APPLY (
    VALUES
        ('Categories', src.Categories),
        ('Orders', src.Orders),
        ('Products', src.Products)
) AS obj([#Object#], [#Count#])

两种方法都能输出你想要的格式：

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