Dynamic TSQL Pivot без агрегатной функции

У меня есть такая таблица ('ExternalPersonRelationTable')

PersonId SubjectCode
4187 3
4187 278
4429 3
4429 4
4463 99
4464 174
4464 175

Я хочу повернуть данные так, чтобы каждый человек в таблице получил столбец и значение ИСТИНА/ЛОЖЬ для каждого кода темы, т.е. такая таблица:

Code 4187 4429 4463 4464
3 TRUE TRUE FALSE FALSE
4 FALSE TRUE FALSE FALSE
...
99 FALSE FALSE TRUE FALSE
...
174 FALSE FALSE FALSE TRUE
175 FALSE FALSE FALSE TRUE

Я понимаю, что это проблема, которую я должен решить с помощью PIVOT и динамического SQL, но я боюсь, что мой опыт ограничен использованием CTE и простых JOIN, поэтому мне трудно PIVOTing данные, не говоря уже о динамическом именовании результирующих столбцов.

SubjectCode и PersonId в конечном итоге будут присоединены к другим таблицам, чтобы получить их реальные значения, но для примера я думаю, что они не нужны.

Как мне это решить?


sql sql-server tsql pivot dynamic-pivot
person Kasper    schedule 04.03.2021    source источник
comment
Вы не можете выполнять сводку без агрегирования. Поворот — это форма агрегации.   -  person Larnu    schedule 05.03.2021
comment
Из того, что я прочитал, можно использовать функцию агрегации MAX (stackoverflow.com/a/1343164/9323877) для выполнять не агрегируя. Но, несмотря на это, если это лает не на то дерево, как мне решить проблему?   -  person Kasper    schedule 05.03.2021
comment
MAX является агрегатной функцией.   -  person Larnu    schedule 05.03.2021
comment
Прошу прощения за неточность вопроса :)   -  person Kasper    schedule 05.03.2021
comment
Я действительно не стал бы идти по пути динамического разворота, особенно если вы плохо знаете процедурный T-SQL. Для этого требуется динамический SQL, который сам по себе сложен в написании и отладке и подвержен ошибкам и внедрению. Имена столбцов должны быть переименованы в клиентском приложении. Вместо этого используйте сводку по номеру строки, чтобы ваши имена столбцов стали Person1,Person2,Person3..., это намного проще сделать и не требует динамического SQL.   -  person Charlieface    schedule 05.03.2021

Ответы (1)


arrow_upward
0
arrow_downward

Пример данных

create table ExternalPersonRelationTable
(
  PersonId int,
  SubjectCode int
);

insert into ExternalPersonRelationTable (PersonId, SubjectCode) values
(4187, 3),
(4187, 278),
(4429, 3),
(4429, 4),
(4463, 99),
(4464, 174),
(4464, 175);

Решение

Начните с (ограниченной) статической версии сводного запроса в качестве эталона.

select piv.SubjectCode as Code,
       isnull(convert(bit, piv.[4187]), 0) as [4187],
       isnull(convert(bit, piv.[4429]), 0) as [4429],
       isnull(convert(bit, piv.[4463]), 0) as [4463],
       isnull(convert(bit, piv.[4464]), 0) as [4464]
from ExternalPersonRelationTable epr
pivot (max(epr.PersonId) for epr.PersonId in ([4187],[4429],[4463],[4464])) piv;

Определите динамические части и создайте (и подтвердите) их.

-- constuct lists
declare @fieldList nvarchar(1000);
declare @pivotList nvarchar(1000);

with cte as
(
  select epr.PersonId
  from ExternalPersonRelationTable epr
  group by epr.PersonId
)
select @fieldList = string_agg('isnull(convert(bit, piv.['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']), 0) as ['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ', ') within group (order by cte.PersonId),
       @pivotList = string_agg('['
                             + convert(nvarchar(10), cte.PersonId)
                             + ']', ',') within group (order by cte.PersonId)
from cte;

-- validate lists
select @fieldList as FieldList;
select @pivotList as PivotList;

Объедините динамические части в окончательный запрос (и проверьте на этапе разработки).

-- construct query
declare @query nvarchar(3000) = 'select piv.SubjectCode as Code, '
                              + @fieldList
                              + 'from ExternalPersonRelationTable epr '
                              + 'pivot (max(epr.PersonId) for epr.PersonId in ('
                              + @pivotList
                              + ')) piv;';
      
-- validate query
select @query as Query;

Запустите динамический запрос.

-- run query
exec sp_executesql @query;

Результат

Code  4187   4429   4463   4464
----  -----  -----  -----  -----
  3   True   True   False  False
  4   False  True   False  False
 99   False  False  True   False
174   False  False  False  True
175   False  False  False  True
278   True   False  False  False

Fiddle, чтобы увидеть все в действии.

person Sander    schedule 05.03.2021