Using pivot and then counting the values in each pivot column

Question

I have a table that looks like this:

Agent_id	break_id	time
1	1	15
1	2	12
1	2	12

I used pivot to get this structure:

Agent_id	1	2
1	15	24

The problem is that I need to get the count for the pivoted columns, in the example I need to have a structure like this:

Agent_id	1	2	count1	count2
1	15	24	1	2

And I'm not sure on how to do it ... this is the query so far.

DECLARE @COLUMNS VARCHAR(MAX)

DECLARE @QUERY nVARCHAR(MAX)

SELECT @COLUMNS = COALESCE(@COLUMNS + ', ','') + QUOTENAME([break_id])
FROM
    (SELECT DISTINCT [break_id] FROM test) AS B
ORDER BY B.[break_id]


  SET @QUERY = '
SELECT agent_id,
    '+@COLUMNS+' 
FROM (
SELECT TOP (1000) 
      agent_id,break_id,time_inbreak 
  FROM test  
) as pivotData 

PIVOT (
    SUM(time_inbreak)
    FOR break_id IN ('+@COLUMNS+') 
) as pivotResult

'

EXEC sp_executesql @QUERY

Any help is greatly appreciated

Answer 1

Unfortunately, PIVOT can only pivot a single column. But we can do multiple columns using conditional aggregation SUM(CASE WHEN... and COUNT(CASE WHEN...:

DECLARE @COLUMNS NVARCHAR(MAX), @QUERY NVARCHAR(MAX);

SELECT @COLUMNS = STRING_AGG(
  CONCAT(
    QUOTENAME(CONCAT('Sum', break_id)),
    ' = SUM(CASE WHEN break_id = ',
    break_id,
    ' THEN time_inbreak END), ',
    QUOTENAME(CONCAT('Count', break_id)),
    ' = COUNT(CASE WHEN break_id = ',
    break_id,
    ' THEN 1 END)'
  )
) WITHIN GROUP (ORDER BY B.break_id)
FROM
    (SELECT DISTINCT break_id FROM test) AS B;


  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