如何在SQL动态透视表中添加行/列/总计

针对动态卖家列的透视需求，我们可以通过动态SQL生成列定义 + UNION ALL合并明细行与总计行的方式实现，具体步骤如下：

完整SQL代码 #

DECLARE @cols AS NVARCHAR(MAX),
        @cols_total AS NVARCHAR(MAX),
        @cols_grand_total AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

-- 生成动态卖家列（用于透视）
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Seller)
                      FROM t
                      GROUP BY Seller
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 生成行总计计算逻辑（各卖家列数值求和）
SELECT @cols_total = STUFF((SELECT ' + ISNULL(' + QUOTENAME(Seller) + ', 0)'
                            FROM t
                            GROUP BY Seller
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 生成总计行的列总计逻辑（统计每个卖家服务的不同客户数）
SELECT @cols_grand_total = STUFF((SELECT ', COUNT(DISTINCT CASE WHEN Seller = ''' + Seller + ''' THEN Customer END) AS ' + QUOTENAME(Seller)
                                  FROM t
                                  GROUP BY Seller
                                  FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 构建最终动态查询
SET @query = N'
-- 客户交互明细行，包含行总计
SELECT 
    Customer,
    ' + @cols + ',
    ' + @cols_total + ' AS Total
FROM (
    SELECT Customer, Seller
    FROM t
) x
PIVOT (
    COUNT(Seller)
    FOR Seller IN (' + @cols + ')
) p
UNION ALL
-- 总计行，包含列总计与总交互次数
SELECT 
    ''Total'' AS Customer,
    ' + @cols_grand_total + ',
    (SELECT COUNT(*) FROM t) AS Total
ORDER BY 
    CASE WHEN Customer = ''Total'' THEN 1 ELSE 0 END,
    Customer'

EXEC sp_executesql @query

代码说明 #

1. 动态列生成：

   • @cols：提取所有唯一卖家名称，转换为带引号的列名格式，用于透视表的列定义。
   • @cols_total：将每个卖家列的数值相加（用ISNULL处理NULL值为0），得到每个客户的总交互次数（行总计）。
   • @cols_grand_total：对每个卖家，通过COUNT(DISTINCT CASE...)统计服务的不同客户数量，作为总计行的列总计值。

2. 查询结构：

   • 第一部分是基础透视查询，返回每个客户与各卖家的交互次数，附加行总计。
   • 第二部分是总计行，返回每个卖家服务的客户数、总交互次数，用UNION ALL合并到结果中。
   • 排序逻辑确保总计行始终显示在结果末尾，其余客户按名称排序。

执行结果 #

Customer | Angle | Anny | Joe | Tedy | Total
Ben      | 0     | 1    | 0   | 0    | 1
Bob      | 0     | 0    | 2   | 0    | 2
Kelly    | 1     | 0    | 0   | 0    | 1
Kime     | 0     | 1    | 0   | 2    | 3
Total    | 1     | 2    | 2   | 2    | 7

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