Dynamic Pivot Table Column Name

In some cases, we need to create SQL Server Pivot table with dynamic columns, however, the SQL Server Pivot requires fixed table names.
I will show you how to work around it.
Consider the table and the data below

Create Table ##DataTable (Sequence int , UserId int, Status int, DtStart datetime )
Insert ##DataTable (Sequence ,UserId ,Status ,DtStart ) values
(1644897 ,420969 ,801 ,'2017-09-20 17:58:18.503')
,(1644896 ,420969 ,812 ,'2017-09-15 08:20:48.390')
,(1644895 ,420969 ,803 ,'2017-09-14 10:13:25.733')
,(1644894 ,420969 ,802 ,'2017-09-14 09:35:57.337')
,(1644893 ,420969 ,801 ,'2017-09-08 18:18:16.490')
,(1644892 ,420968 ,801 ,'2017-09-20 17:55:02.660')
,(1644891 ,420967 ,801 ,'2017-09-20 17:47:52.340')
,(1644890 ,420966 ,801 ,'2017-09-20 17:40:22.580')
,(1644880 ,420965 ,803 ,'2017-09-20 17:05:30.870')
,(1644879 ,420965 ,801 ,'2017-09-20 17:05:29.130')
,(1644877 ,420964 ,801 ,'2017-09-20 17:02:16.830')

We need get all status ordered by the date, also it is repeated

First, we will create a table to gather only the information that we will work,

CREATE TABLE ##tmpJobStatusLog (JobNo int, Status int, Sequence int , StatusName varchar(20) )

;WITH cte
AS (SELECT *,
Sequence = Row_number()
OVER (
partition BY jobno
ORDER BY entered)
FROM ##jobstatuslog)
INSERT ##tmpjobstatuslog
SELECT jobno,
status,
sequence,
'Status' + Cast(sequence AS VARCHAR(9)) StatusName
FROM cte
ORDER BY jobno,
sequence

Second, we will create a table to gather the column names

CREATE TABLE ##StatusSequence(Sequence int, StatusName varchar(20))
INSERT ##StatusSequence
SELECT DISTINCT sequence,
statusname

FROM ##tmpjobstatuslog 

Third, the create the dynamic table

declare @sql nvarchar(max) =''
declare @columnsStatus nvarchar(max) =''

Select @columnsStatus = @columnsStatus + N'[' + StatusName + N'],' from ##StatusSequence

SET @sql = N';
select JobNo,' + Left(@columnsStatus, Len(@columnsStatus) - 1) + N' from (
select a.JobNo,b.StatusName, a.Status from ##tmpJobStatusLog a left join ##StatusSequence b on a.Sequence = b.Sequence
) as St pivot ( max(Status) for StatusName in ( ' + Left(@columnsStatus, Len(@columnsStatus) - 1) + N') ) pvt '

exec sp_executesql @sql

Advertisements