SQL Server过滤带有数据透视表的动态句子中的零值

Question

我在SQL Server中使用一个工作查询，并使用动态旋转来查看一个名为Referencia的列，在接下来的列中有一些PC名称。我们的想法是使用SQL语句在Grafana中获取条形图。我使用的SQL语句如下:

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);

得到的表的一个例子是:

tbody> <<tr>221479 - u221519 - 9

reference	PCHQ0197	PCHQ0215	PCHQ0272
221469 - 1	1	2	3
	3	2	1
	0	0	0

您需要对动态SQL

中的值执行WHERE

过滤器。如果您使用条件聚合使用手动枢轴，这将更容易做到。

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
DECLARE @PC AS NVARCHAR(MAX) = 'PCHQ0197,PCHQ0215,PCHQ0272';
SELECT @cols = STRING_AGG('
' + QUOTENAME(PC) + ' = COUNT(CASE WHEN PC = ' + QUOTENAME(PC, '''') + ' THEN 1 END)', ',')
FROM (
SELECT 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, ','))
) dg
);
SET @query = '
SELECT
Referencia, ' + @cols + '
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, '',''))
GROUP BY Referencia
ORDER BY Referencia;
';
PRINT @query;
EXEC sp_executesql @query
N'@PC nvarchar(max)',
@PC;

老实说，使用索引良好的表值参数可能比依赖STRING_SPLIT更有效。