SQL Server动态透视表过滤PC列全零的Referencia行

现有SQL Server动态透视查询可获取Referencia列及指定PC名称列的数据，用于在Grafana生成柱状图。当前查询运行正常，但需过滤掉所有PC列值均为0的Referencia行。

原查询代码 #

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
DECLARE @PC AS NVARCHAR(MAX) = 'PCHQ0197,PCHQ0215,PCHQ0272';

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(PC)
                   FROM DATOS_GENERALES DG INNER JOIN TABLA_REFERENCIAS TR ON DG.RefId = TR.Id
                   WHERE TimeStamp_UTC IS NOT NULL AND PC IN (SELECT value FROM STRING_SPLIT(@PC, ','))
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

SET @query = 'SELECT Referencia, ' + @cols + '
              FROM
              (
              SELECT PC,
                     TR.Referencia AS Referencia
              FROM DATOS_GENERALES DG INNER JOIN TABLA_REFERENCIAS TR ON DG.RefId = TR.Id
              WHERE TimeStamp_UTC IS NOT NULL
              ) AS A
              PIVOT
              (
                  COUNT(PC) FOR PC IN (' + @cols + ')
              ) AS P
              ORDER BY Referencia'
EXECUTE(@query);

解决方案 #

要过滤掉所有PC列值均为0的行，需在动态查询中添加WHERE条件，判断至少有一个PC列的值大于0。由于PC列是动态生成的，我们可以基于@cols变量动态拼接过滤逻辑：

修改后的完整代码 #

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
DECLARE @PC AS NVARCHAR(MAX) = 'PCHQ0197,PCHQ0215,PCHQ0272';

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(PC)
                   FROM DATOS_GENERALES DG INNER JOIN TABLA_REFERENCIAS TR ON DG.RefId = TR.Id
                   WHERE TimeStamp_UTC IS NOT NULL AND PC IN (SELECT value FROM STRING_SPLIT(@PC, ','))
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

-- 生成过滤条件：至少一个PC列值大于0
DECLARE @filter AS NVARCHAR(MAX) = REPLACE(@cols, ',', ' > 0 OR ') + ' > 0';

SET @query = 'SELECT Referencia, ' + @cols + '
              FROM
              (
              SELECT PC,
                     TR.Referencia AS Referencia
              FROM DATOS_GENERALES DG INNER JOIN TABLA_REFERENCIAS TR ON DG.RefId = TR.Id
              WHERE TimeStamp_UTC IS NOT NULL
              ) AS A
              PIVOT
              (
                  COUNT(PC) FOR PC IN (' + @cols + ')
              ) AS P
              WHERE ' + @filter + ' -- 添加过滤条件
              ORDER BY Referencia'
EXECUTE(@query);

关键修改说明 #

1. 新增@filter变量：通过替换@cols中的逗号为> 0 OR，再拼接 > 0，生成类似[PCHQ0197] > 0 OR [PCHQ0215] > 0 OR [PCHQ0272] > 0的条件。
2. 在动态查询的PIVOT结果后添加WHERE ' + @filter + '，过滤掉所有PC列值均为0的行。

修改后，查询只会保留至少有一个PC列值非零的Referencia行，符合需求。

内容的提问来源于stack exchange，提问作者Jose Mari Muguruza