Функция Pivot возвращает только общую сумму

Я пытаюсь понять, как работает сводная таблица.

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

Как я могу получить тот же результат синтаксиса регистра суммы?


sql sql-server pivot dynamic-pivot
person Nicola Cossu    schedule 16.12.2020    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы должны включить идентификатор в подзапрос:

  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
person Antonio Tolusic    schedule 16.12.2020
comment
Большое спасибо. В моем реальном случае я уже выполнил динамическую часть, но застрял с проблемой, которую вы решили с помощью поля id. С наилучшими пожеланиями. - person Nicola Cossu; 16.12.2020

arrow_upward
1
arrow_downward

Синтаксис 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);
person GMB    schedule 16.12.2020
comment
Вероятно, вы хотите убедиться, что вы правильно указали / экранировали значение technician. Я подозреваю, что это скорее имя, и они могут легко содержать одинарные кавычки ('). Учитывая, что в OP указано, что значение может содержать до 1 миллиарда символов, я бы посоветовал его действительно правильно указать. - person Larnu; 16.12.2020
comment
Спасибо даже вам GMB. Вы рулите!!! - person Nicola Cossu; 16.12.2020