Pivot function returns only grand total

Question

I'm trying to understand how pivot table works.

IF OBJECT_ID(N'tempdb..#exams') IS NOT NULL
BEGIN
DROP TABLE #exams
END
GO

create table #exams (
id uniqueidentifier,
exam nvarchar(max),
technician nvarchar(max)
)

insert into #exams 
values 
(newid(),'Esame1','Tecnico1'),
(newid(),'Esame2','Tecnico1'),
(newid(),'Esame1','Tecnico2'),
(newid(),'Esame3','Tecnico1'),
(newid(),'Esame3','Tecnico2'),
(newid(),'Esame3','Tecnico3'),
(newid(),'Esame3','Tecnico1')

I kwow how to use sum case in order to get what I'm looking for:

select
 exam,
 sum(case when technician = 'Tecnico1' then 1 else 0 end) as Tecnico1,
 sum(case when technician = 'Tecnico2' then 1 else 0 end) as Tecnico2,
 sum(case when technician = 'Tecnico3' then 1 else 0 end) as Tecnico3
 from #exams
 group by exam
 order by exam

exam    Tecnico1    Tecnico2    Tecnico3
Esame1    1            1            0
Esame2    1            0            0
Esame3    2            1            1

By the way I have a lot of technician and I'd like to automate my queries with dynamic columns.

When I try pivot syntax

    select * from (
        select exam,
               technician 
               from #exams
        ) as t
        pivot 
        (   count(exam)
                for technician in (Tecnico1,Tecnico2,Tecnico3)
            ) as t

I get only grand total

Tecnico1    Tecnico2    Tecnico3
   4          2            1

How can I get the same result of the sum case syntax?

Answer 1

You have to include id in subquery:

  select * from (
    select id,exam,
           technician 
           from #exams
    ) as t
    pivot 
    (   count(id)
            for technician in (Tecnico1,Tecnico2,Tecnico3)
        ) as t

Dynamic:

DECLARE @sql  nvarchar(max);
DECLARE @columnname nvarchar(max);


SELECT @columnname=COALESCE(@columnname+ ',', '') + QUOTENAME(CAST(q.technician AS nvarchar(20)),'[]')
FROM
(SELECT DISTINCT e.technician
FROM #exams e) AS q
 

 SET @sql=
'select * from (
  select id,exam,
         technician 
         from #exams
  ) as t
  pivot 
  (   count(id)
          for technician in ('+@columnname+')
      ) as t'

EXECUTE sp_executesql @sql

Answer 2

The pivot syntax does not support passing the list of values as a subquery. That's a typical limitation in SQL: a query must return a fixed set of columns.

What you ask for requires dynamic SQL. In SQL Server, that would look like:

declare @sql  as nvarchar(max);

select @sql = 
    'select exam, ' 
    + string_agg(
    'sum(case when technician = ''' + technician + ''' then 1 else 0 end) as [' + technician + ']',
    ', '
    )
    + ' from #exams group by exam'
from #exams;
    
execute(@sql);