使用pivot，然后对每个pivot列中的值进行计数

Question

我有一个表，看起来像这样:

不幸的是，PIVOT只能透视单列。但是我们可以使用条件聚合SUM(CASE WHEN...和COUNT(CASE WHEN...做多列:

DECLARE @COLUMNS VARCHAR(MAX)
DECLARE @QUERY nVARCHAR(MAX)
SELECT @COLUMNS = COALESCE(@COLUMNS + ', ','') +
'Sum' + QUOTENAME([break_id]) +
' = SUM(CASE WHEN break_id = ' + break_id + ' THEN time_inbreak END), Count' +
QUOTENAME([break_id]) + ' = COUNT(CASE WHEN break_id = ' + break_id + ' THEN 1 END)'
FROM
(SELECT DISTINCT [break_id] FROM test) AS B
ORDER BY B.[break_id]
OPTION (MAXDOP 1);

SET @QUERY = '
SELECT agent_id,
'+@COLUMNS+' 
FROM (
SELECT TOP (1000) 
agent_id,break_id,time_inbreak 
FROM test  
) as pivotData 
GROUP BY agent_id;
';
PRINT @QUERY
EXEC sp_executesql @QUERY

我必须说，我不确定像这样聚合列有多安全，尤其是在并行的情况下。最好使用STRING_AGG或FOR XML PATH(''), TYPE。至少我已经添加了OPTION (MAXDOP 1)来防止并行