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