Pivot函数只返回总计

Question

我正在努力了解透视表是如何工作的。

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

我知道如何使用总和案例来获得我想要的东西：

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

顺便说一句，我有很多技术人员，我想用动态列自动化我的查询。

当我尝试透视语法时

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

我只得到总

Tecnico1    Tecnico2    Tecnico3
4          2            1

我怎样才能得到和大小写语法相同的结果？

Answer 1

您必须在子查询中包含id：

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

动态：

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

pivot语法不支持将值列表作为子查询传递。这是SQL中的一个典型限制：查询必须返回一组固定的列。

您所要求的内容需要动态SQL。在SQL Server中，这看起来像：

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