SQL动态透视表开发求助：按公司组统计月度产品数据

我来帮你搞定这个动态透视表的问题，你的现有代码确实存在列缺失、缺少净量列和总计的问题，下面我会一步步梳理需求，给出改进后的完整代码和详细说明。

核心需求回顾 #

• 按公司组统计月度产品销售/取消情况：
  • Active（活跃）产品：按dBillDate统计数量
  • Cancelled（已取消）产品：按dtContractCancelledBilled统计数量
• 空白值显示0，每个产品需添加净量列（活跃数-取消数）
• 需包含各列小计及整体总计行

期望输出样式 #

Pd1Actv  Pd1Cancd  Pd1Net  Prd2Actv  Prd2Cancd  Pd2Net  Total
Comp1    6         5       1         15         0       15     16
Comp2    20        6       14        39         0       39     53
Comp3    63        14      49        82         0       82     131
Total    89        25      64        136        0       136    200

数据源表结构 #

SELECT [iCompId],[sContractStatusCode],[sContractStatusDesc],[dBillDate],dtContractCancelledBilled 
FROM [Contract_Header]

现有代码的问题 #

你的现有代码存在以下几个关键问题：

1. 仅统计了有数据的产品状态列，导致部分产品的取消列缺失
2. 未实现每个产品的净量计算列
3. 没有添加总计行和各列小计
4. 日期条件仅过滤了dBillDate，未考虑取消产品的dtContractCancelledBilled日期

改进后的完整代码 #

DECLARE @cols AS NVARCHAR(MAX),
        @colsTotal AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX),
        @sCompGroupNumber AS VARCHAR(20) = 'DG10000174',
        @startdate AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE),
        @enddate AS DATE = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

-- 获取指定公司组内所有涉及的产品（包含活跃和取消状态）
WITH AllProducts AS (
    SELECT DISTINCT c.sProductCode
    FROM [Contract_Header] c
    INNER JOIN [Comp_Header] d ON d.iid = c.iCompId
    INNER JOIN [Comp_CompGroupLink] dgl ON d.iId = dgl.CompId
    INNER JOIN [Comp_CompGroups] dg ON dgl.iCompGroupId = dg.iId
    WHERE c.sContractStatusCode IN ('A', 'C')
      AND (
          (c.dBillDate BETWEEN @startdate AND @enddate) 
          OR (c.dtContractCancelledBilled BETWEEN @startdate AND @enddate)
      )
      AND dg.sCompGroupNumber = @sCompGroupNumber
),
-- 生成所有需要的列：活跃列、取消列、净量列
AllColumns AS (
    SELECT QUOTENAME(p.sProductCode + 'Actv') AS ColName, p.sProductCode, 'Actv' AS ColType FROM AllProducts p
    UNION ALL
    SELECT QUOTENAME(p.sProductCode + 'Cancd') AS ColName, p.sProductCode, 'Cancd' AS ColType FROM AllProducts p
    UNION ALL
    SELECT QUOTENAME(p.sProductCode + 'Net') AS ColName, p.sProductCode, 'Net' AS ColType FROM AllProducts p
)
-- 构建动态列字符串：所有列、总计计算列
SELECT 
    @cols = STRING_AGG(ColName, ', ') WITHIN GROUP (ORDER BY sProductCode, ColType),
    @colsTotal = STRING_AGG(CASE WHEN ColType IN ('Actv', 'Cancd') THEN ColName END, ' + ') WITHIN GROUP (ORDER BY sProductCode)
FROM AllColumns

-- 构建最终动态查询语句
SET @query = N'
WITH ContractData AS (
    SELECT 
        d.sCompName,
        c.sProductCode,
        -- 按状态和对应日期统计计数
        CASE WHEN c.sContractStatusCode = ''A'' AND c.dBillDate BETWEEN @startdate AND @enddate THEN 1 ELSE 0 END AS ActvCount,
        CASE WHEN c.sContractStatusCode = ''C'' AND c.dtContractCancelledBilled BETWEEN @startdate AND @enddate THEN 1 ELSE 0 END AS CancdCount
    FROM [Contract_Header] c
    INNER JOIN [Comp_Header] d ON d.iid = c.iCompId
    INNER JOIN [Comp_CompGroupLink] dgl ON d.iId = dgl.CompId
    INNER JOIN [Comp_CompGroups] dg ON dgl.iCompGroupId = dg.iId
    WHERE dg.sCompGroupNumber = @sCompGroupNumber
      AND c.sContractStatusCode IN (''A'', ''C'')
),
-- 按公司+产品聚合数据，同时添加总计行
AggregatedData AS (
    SELECT 
        sCompName,
        sProductCode,
        SUM(ActvCount) AS ActvTotal,
        SUM(CancdCount) AS CancdTotal,
        SUM(ActvCount) - SUM(CancdCount) AS NetTotal
    FROM ContractData
    GROUP BY sCompName, sProductCode
    UNION ALL
    -- 总计行：按产品聚合所有公司的数据
    SELECT 
        ''Total'' AS sCompName,
        sProductCode,
        SUM(ActvCount) AS ActvTotal,
        SUM(CancdCount) AS CancdTotal,
        SUM(ActvCount) - SUM(CancdCount) AS NetTotal
    FROM ContractData
    GROUP BY sProductCode
),
-- 透视数据，将产品状态列转为横向
PivotedData AS (
    SELECT 
        sCompName,
        ' + @cols + ',
        -- 计算每行的总合计（活跃+取消的总和）
        ' + @colsTotal + ' AS Total
    FROM (
        SELECT 
            sCompName,
            ColName = sProductCode + CASE Metric 
                WHEN ''ActvTotal'' THEN ''Actv'' 
                WHEN ''CancdTotal'' THEN ''Cancd'' 
                ELSE ''Net'' 
            END,
            MetricValue
        FROM AggregatedData
        UNPIVOT (
            MetricValue FOR Metric IN (ActvTotal, CancdTotal, NetTotal)
        ) up
    ) src
    PIVOT (
        SUM(MetricValue) FOR ColName IN (' + REPLACE(@cols, ', ', ',') + ')
    ) piv
)
-- 将NULL替换为0，并排序（总计行放在最后）
SELECT 
    sCompName,
    ' + REPLACE(@cols, ', ', ', ISNULL(', ', 0) ') + ',
    ISNULL(Total, 0) AS Total
FROM PivotedData
ORDER BY CASE WHEN sCompName = ''Total'' THEN 1 ELSE 0 END, sCompName'

-- 执行动态SQL，传递参数避免注入风险
EXEC sp_executesql @query, 
    N'@sCompGroupNumber VARCHAR(20), @startdate DATE, @enddate DATE',
    @sCompGroupNumber = @sCompGroupNumber,
    @startdate = @startdate,
    @enddate = @enddate

关键改进说明 #

1. 确保所有列不缺失：通过AllProducts CTE获取指定公司组内所有涉及的产品，然后为每个产品生成活跃、取消、净量列，不管该产品是否有对应状态的数据。
2. 正确的日期统计：针对活跃和取消状态分别使用对应的日期字段（dBillDate和dtContractCancelledBilled）进行计数，避免统计错误。
3. 净量列计算：在聚合阶段直接计算活跃数-取消数，再通过逆透视+透视的方式整合到结果中。
4. 总计行实现：通过UNION ALL添加总计行，最后排序时将总计行放在所有公司行的后面。
5. NULL值处理：使用ISNULL将透视后的NULL值替换为0，满足"未销售/取消则显示0"的需求。
6. SQL注入防护：使用参数化传递@sCompGroupNumber、@startdate和@enddate，避免动态SQL的注入风险。
7. 代码可读性优化：使用CTE拆分逻辑，用STRING_AGG（SQL Server 2017+支持）替代传统的STUFF+XML PATH构建动态列，代码更简洁易维护。

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