How to perform a dynamic pivot in T-SQL on a subset of columns

Question

Software used: SQL Server 2014 Management Studio to query a standard 64-bit SQL Server.

I'm trying to create a query that generates a set of patient assessment results across multiple patients / assessments, and to put the results of each assessment on one row. This would suggest the use of pivoting of some sort.

Each assessment consists of a number of tests, each of which has a type, a name and a score. It is possible that we may want to add more tests to an assessment at some point, which would suggest the use of dynamic pivots.

At the moment, my query essentially looks like this:

SELECT TOP 20000
    P.PatientId,
    P.LastName,
    P.FirstName,
    FORMAT(P.DateOfBirth, 'dd/MM/yyyy') as 'DateOfBirth',
    FORMAT(A.TreatmentDate, 'dd/MM/yyyy') as 'AssmentDate',
    A.TestType,
    A.TestName,
    A.TestScore
FROM
    Patient P
INNER JOIN 
    Assessment A ON (A.PatientSerialNumber = P.PatientSerialNumber)
INNER JOIN 
    AssessmentHeader AH ON (AH.AssessmentSerialNumber = A.AssessmentSerialNumber 
                            AND AH.PatientSerialNumber = P.PatientSerialNumber)
WHERE
    A.ValidEntryIndicator = 'Y'
    AND AH.ValidEntryIndicator = 'Y'
ORDER BY
    P.PatientId, T.TreatmentDate

My results essentially look like this (there are actually a lot more test types and names than shown here - these are just for illustration):

PatientId	LastName	FirstName	DateOfBirth	AssmentDate	TestType	TestName	TestScore
AB1234	PATIENT	Test	1/1/2000	1/1/2020	Renal	Urgency	0
AB1234	PATIENT	Test	1/1/2000	1/1/2020	Renal	Retention	1
AB1234	PATIENT	Test	1/1/2000	1/1/2020	GI	Proctitis	2
AB1234	PATIENT	Test	1/1/2000	1/1/2020	GI	Diarrhea	3
AB1234	PATIENT	Test	1/1/2000	6/6/2021	Renal	Urgency	1
AB1234	PATIENT	Test	1/1/2000	6/6/2021	Renal	Retention	0
AB1234	PATIENT	Test	1/1/2000	6/6/2021	GI	Proctitis	1
AB1234	PATIENT	Test	1/1/2000	6/6/2021	GI	Diarrhea	2
YZ6789	PATIENT2	Test2	1/1/1999	7/7/2020	Renal	Urgency	2
YZ6789	PATIENT2	Test2	1/1/1999	7/7/2020	Renal	Retention	5
YZ6789	PATIENT2	Test2	1/1/1999	7/7/2020	GI	Proctitis	3
YZ6789	PATIENT2	Test2	1/1/1999	7/7/2020	GI	Diarrhea	1
YZ6789	PATIENT2	Test2	1/1/1999	6/7/2021	Renal	Urgency	2
YZ6789	PATIENT2	Test2	1/1/1999	6/7/2021	Renal	Retention	9
YZ6789	PATIENT2	Test2	1/1/1999	6/7/2021	GI	Proctitis	4
YZ6789	PATIENT2	Test2	1/1/1999	6/7/2021	GI	Diarrhea	5

What I would like is this:

PatientId	LastName	FirstName	DateOfBirth	AssmentDate	Renal-Urgency	Renal-Retention	GI-Proctitis	GI-Diarrhea
AB1234	PATIENT	Test	1/1/2000	1/1/2020	0	1	2	3
AB1234	PATIENT	Test	1/1/2000	6/6/2021	1	0	1	2
YZ6789	PATIENT2	Test2	1/1/1999	7/7/2020	2	5	3	1
YZ6789	PATIENT2	Test2	1/1/1999	6/7/2021	2	9	4	5

I've tried to follow various online resources and SO question/answers (this one looked the most promising), but I just can't seem to get the methods shown in these to work with my query (frustratingly)

Can anyone help me out?

First question: Why are you trying to do dynamic pivoting with a SQL query? *Usually* this kind of variable-category cross-tabulation is better handled by client code that consumes your query above and then cross-tabulates it using a dictionary-like structure. Because ultimately a 200+ column report is virtually unreadable and unusable on it's own, it almost always requires some kind of client tool or app (like Excel, etc.) to make it comprehensible. — RBarryYoung, Jul 16 '21 at 12:05
@Campbell Reid can you give me the table and sample data script (Create and Insert statements) so I can write this dynamically — Gudwlk, Jul 16 '21 at 12:37

score 0 · Answer 1 · answered Jul 16 '21 at 12:52

So you can use the technique shown at the linked answer, you just need to extend it to handle multiple key-columns and multiple pivot-source columns:

CREATE TABLE #Table (
        -- key columns
        PatientId Varchar(8),
        LastName VarChar(32),
        FirstName Varchar(32),
        DateOfBirth DateTime,
        AssmentDate DateTime,
        -- pivot-source columns
        TestType Varchar(20),
        TestName Varchar(20),
        -- value column
        TestScore INT
)
go

INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'Renal', 'Urgency', '0');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'Renal', 'Retention', '1');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'GI', 'Proctitis', '2');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'GI', 'Diarrhea', '3');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'Renal', 'Urgency', '1');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'Renal', 'Retention', '0');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'GI', 'Proctitis', '1');
INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'GI', 'Diarrhea', '2');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'Renal', 'Urgency', '2');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'Renal', 'Retention', '5');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2 ', '1/1/1999', '7/7/2020', 'GI', 'Proctitis', '3');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'GI', 'Diarrhea', '1');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'Renal', 'Urgency', '2');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'Renal ', 'Retention', '9');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'GI', 'Proctitis', '4');
INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'GI', 'Diarrhea', '5');
go

DECLARE @cols NVARCHAR(2000) 
DECLARE @query NVARCHAR(4000) 

SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT 
                           '],[' + t.TestType + '-' + t.TestName
                        FROM    #Table AS t 
                        FOR XML PATH('') 
                      ), 1, 2, '') + ']' 
SELECT  @cols

SET @query = N'SELECT PatientId, LastName, FirstName, DateOfBirth, AssmentDate, '
    + @cols 
    +' FROM 
(SELECT PatientId, LastName, FirstName, DateOfBirth, AssmentDate, '
    +' TestType + ''-'' + TestName AS ColName, TestScore FROM #Table AS t1) p 
PIVOT (MAX([TestScore]) FOR ColName IN ( '+ @cols +' )) 
AS pvt;' 

PRINT(@query)
EXECUTE(@query)

DROP TABLE #Table
GO

In your case, you would use the query you have in your question to load #Table. I had to guess about the length of the Varchar(..) columns, so you will need to check and correct them.

Gudwlk · Answer 2 · 2021-07-17T01:54:16.283

Pivot is based on column values so the answer depends on what values are there in the columns.

  CREATE TABLE #Table (
    -- key columns
    PatientId Varchar(8),
    LastName VarChar(32),
    FirstName Varchar(32),
    DateOfBirth DateTime,
    AssesmentDate DateTime,
    -- pivot-source columns
    TestType Varchar(20),
    TestName Varchar(20),
    -- value column
    TestScore INT
 )
go

   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'Renal', 'Urgency', '0');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'Renal', 'Retention', '1');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'GI', 'Proctitis', '2');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '1/1/2020', 'GI', 'Diarrhea', '3');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'Renal', 'Urgency', '1');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'Renal', 'Retention', '0');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'GI', 'Proctitis', '1');
   INSERT INTO #Table Values('AB1234', 'PATIENT', 'Test', '1/1/2000', '6/6/2021', 'GI', 'Diarrhea', '2');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'Renal', 'Urgency', '2');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'Renal', 'Retention', '5');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2 ', '1/1/1999', '7/7/2020', 'GI', 'Proctitis', '3');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '7/7/2020', 'GI', 'Diarrhea', '1');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'Renal', 'Urgency', '2');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'Renal', 'Retention', '9');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'GI', 'Proctitis', '4');
   INSERT INTO #Table Values('YZ6789', 'PATIENT2', 'Test2', '1/1/1999', '6/7/2021', 'GI', 'Diarrhea', '5');
 go

  --select * from  #Table

 DECLARE @SQL  AS VARCHAR(MAX)
, @cols_ AS  VARCHAR(MAX) 

 --Making the column list dynamically 
   SELECT @cols_   = STUFF((SELECT DISTINCT ', '+QUOTENAME(  C.TestType +'-'+C.TestName) 
           FROM  #Table  C                 
           FOR XML PATH('')), 1, 1, '')              

--preparing PIVOT query dynamically. 
 select @cols_  --View the columns
   SET @SQL = ' SELECT
          pivoted.PatientId
         ,pivoted.FirstName
         ,pivoted.LastName
         ,pivoted.DateOfBirth
         ,pivoted.AssesmentDate
        ,'+@cols_+'
        FROM 
          (
           SELECT   PatientId ,
    LastName ,
    FirstName,
    DateOfBirth,
    AssesmentDate,        
    TestType+''-''+ TestName as  [PivotedCol],        
    TestScore  
           FROM   #Table 
          ) AS cp
          PIVOT
           (
              MAX([CP].[TestScore]) 
              FOR  [CP].[PivotedCol] IN (' + @cols_ + ')
          ) AS pivoted;  ' ;

 PRINT(@SQL)
 EXECUTE(@SQL)

 DROP TABLE #Table

How to perform a dynamic pivot in T-SQL on a subset of columns

2 Answers2