SQL Server filter zero values in dynamic sentence with pivot table

Question

I am taking a working query in SQL Server and using dynamic pivoting to see a column named Referencia with some PC names in the next columns. The idea is to use the SQL sentence to obtain then a bar chart in Grafana. The SQL sentence that I am using is the following:

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

An example of the obtained table is:

Referencia	PCHQ0197	PCHQ0215	PCHQ0272
221469-1	1	2	3
221479-U	3	2	1
221519-9	0	0	0

This sentence itself works as expected, but now I would like to filter the resulting table so that I only see those rows of Referencia having not zero values in the PC columns.

The obtained result should look as follows:

Referencia	PCHQ0197	PCHQ0215	PCHQ0272
221469-1	1	2	3
221479-U	3	2	1

Answer 1

You need to do a WHERE filter on the values within the dynamic SQL

This is much easier to do if you use a manual pivot using conditional aggregation.

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;

To be honest, it's probably more efficient to use a well-indexed Table-Valued Parameter, rather than relying on STRING_SPLIT.