Dynamic TSQL Pivot without aggregate function

Question

I have a table like this ('ExternalPersonRelationTable')

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

I want to rotate the data so that every person in the table gets a column and a TRUE/FALSE value for each subject code, i.e. a table like this:

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

I gather this is a problem I should solve using PIVOT and dynamic SQL, but I'm afraid my experience is limited to using CTE's and simple JOINs, so I am having a hard time PIVOTing the data, never mind dynamically naming the resulting columns.

The SubjectCode and PersonId will eventually be joined to other tables in order to fetch their real values, but for the example I think they are unnecessary.

How do I solve this?

You *can't* pivot without aggregation. Pivoting is a form of aggregation. — Thom A, Mar 04 '21 at 22:47
From what I've read it's possible to use the MAX aggregation function (https://stackoverflow.com/a/1343164/9323877) to accomplish not aggregating. But notwithstanding that, if that is barking up the wrong tree, how would I go about solving the problem? — Kasper, Mar 04 '21 at 22:49
I really wouldn't go down the road of a dynamic pivot, especially if you don't know procedural T-SQL that well. It requires dynamic SQL, which is itself difficult to write and debug, and is prone to errors and injection. Column names should be renamed in the client application. Instead use a rownumber pivot, so your column names become `Person1,Person2,Person3...` this is much easier to do and does not require dynamic SQL — Charlieface, Mar 05 '21 at 00:14

score 0 · Accepted Answer · answered Mar 05 '21 at 00:16

Sample data

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

Solution

Start with a (limited) static version of the pivot query as a reference.

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;

Identify the dynamic parts and construct (and validate) those.

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

Merge the dynamic parts in the final query (and validate during development phase).

-- 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 the dynamic query.

-- run query
exec sp_executesql @query;

Result

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 to see things in action.

Dynamic TSQL Pivot without aggregate function

1 Answers1