Dynamic Pivot Table across multiple columns

Question

I'm a fairly new to SQL and could use some help. I have a table of time sheet data with a separate time sheet on each row. Each time sheet has a column with jobcode1 to jobcode16 that stores a string indicating a job code. Each of those has a corresponding TotalJob1 to TotalJob16.

I've managed to create a pivot on the JobCode1 no problem with a column for each Job and the total from TotalJob1. I used this to build it.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(TS_Job1Code)
FROM (SELECT DISTINCT TS_Job1Code FROM 
dbo.timesheetData) as timesheetdata

SET @DynamicPivotQuery = 
N'SELECT VolumeID, ' + @ColumnName + '
FROM dbo.timesheetData
PIVOT(SUM(TS_TotalJob1)
FOR TS_Job1Code IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery

I'm struggling to iterate over the other Job columns and merge them into one big pivot table and was hoping someone might be able to give me a pointer?

My thought was to try and repeat the step 16 times but I don't think this is even close to the right way.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @N AS INT
DECLARE @NCHAR AS NVARCHAR(MAX)
SET @N = 1

WHILE @N < 17
BEGIN
SET @NCHAR = CAST(@N as VARCHAR)
SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME(('TS_Job' + 
@NCHAR + 'Code'))
FROM (SELECT DISTINCT ('TS_Job' + @NCHAR + 'Code') FROM 
dbo.timesheetData) as timesheetdata

SET @DynamicPivotQuery = 
N'SELECT ' + @ColumnName + '
FROM dbo.timesheetData
PIVOT(SUM(TS_TotalJob' + @NCHAR + ')
    FOR TS_Job' + @NCHAR + 'Code IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery
SET @N = @N + 1
END
EXEC sp_executesql @SQL

Original

+-------------+----------+----------+----------+-----------+-----------+-----------+
| TimesheetID | JobCode1 | JobCode2 | JobCode3 | TotalJob1 | TotalJob2 | TotalJob3 |
+-------------+----------+----------+----------+-----------+-----------+-----------+
| 1           | J1       | J3       |          | 10        | 9         |           |
+-------------+----------+----------+----------+-----------+-----------+-----------+
| 2           | J2       | J1       | J3       | 5         | 5         | 5         |
+-------------+----------+----------+----------+-----------+-----------+-----------+
| 3           | J2       |          |          | 6         | 3         | 1         |
+-------------+----------+----------+----------+-----------+-----------+-----------+

What I want to achieve

+-------------+----+----+----+----+----+
| TimesheetID | J1 | J2 | J3 | J4 | J6 |
+-------------+----+----+----+----+----+
| 1           | 10 |    | 9  |    |    |
+-------------+----+----+----+----+----+
| 2           | 5  | 5  | 5  |    |    |
+-------------+----+----+----+----+----+
| 3           |    | 6  |    | 3  | 1  |
+-------------+----+----+----+----+----+

Either that 16 times hard-coded or you will need to create a `cursor` selecting all names of the columns, `TS_Job1Code, TS_Job2Code etc` and in the body of the cursor create the `dynamic SQL query`. It is not that bad regarding performance, since it will just iterate 16 times and you will only use the cursor **to create** the dynamic SQL. — Rigerta, May 16 '17 at 13:39
Having 16 `PIVOT`'s on a query, on the other hand, needs to be checked how it will be performance-wise in your case. *I am not sure if you cannot achieve what you need in some other way.* — Rigerta, May 16 '17 at 13:39
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Eli, May 16 '17 at 13:52
@TabAlleman I've amended the original question. Thanks for replying — Harry Murphy, May 16 '17 at 15:05

score 0 · Accepted Answer · answered May 16 '17 at 15:16

0

It's going to get painfully complicated, but one thing you can do is to UNPIVOT your data so that it looks like this:

TimesheetId    JobCode    JobTotal
1              J1         10
1              J3         9
2              J1         5
....

And then PIVOT that derived table to get your desired result.

answered May 16 '17 at 15:16

Tab Alleman

31,483
7
36
52

Fantastic! thank you, this worked for me. Thanks again for taking the time to reply, I really appreciate it. – Harry Murphy May 17 '17 at 07:54

score 0 · Answer 2 · answered May 16 '17 at 19:30

Like Tab mentioned, you can unpivot your data first and then Pivot it again.

You can use CROSS APPLY and VALUES to unpivot your table into a temp table.

SELECT ca.* 
INTO #temp
FROM timesheet
CROSS APPLY (VALUES
    (TimesheetID, JobCode1, TotalJob1), 
    (TimesheetID, JobCode2, TotalJob2), 
    (TimesheetID, JobCode3, TotalJob3)
) ca(TimesheetID, JobCode, TotalJob)

this gives you a table like

TimesheetID JobCode TotalJob
----------- ------- -----------
1           J1      10
1           J3      9
1           NULL    NULL
2           J2      5
2           J1      5
2           J3      5
3           J2      6
3           NULL    3
3           NULL    1

not sure if you'd have null jobcodes in actual data.. but you can eliminate them using Where ca.JobCode IS NOT NULL above

Then you create your dynamic column string from the temp table

DECLARE @JobCodes nvarchar(MAX)
SELECT  @JobCodes = COALESCE(@JobCodes + ',','') + QUOTENAME(JobCode)
FROM       #temp
GROUP BY JobCode
ORDER BY JobCode

Then build your dynamic pivot. Instead of creating global temp table, just use the same query before in your pivot query.

DECLARE @Sql nvarchar(max)
SET @Sql = N'
    SELECT TimeSheetID,' + @JobCodes 
    + 'FROM (
       SELECT ca.* 
       FROM timesheet
       CROSS APPLY (VALUES
          (TimesheetID, JobCode1, TotalJob1), 
          (TimesheetID, JobCode2, TotalJob2), 
          (TimesheetID, JobCode3, TotalJob3)
       ) ca(TimesheetID, JobCode, TotalJob)
    ) t
    PIVOT (
       SUM(TotalJob)
       FOR JobCode IN (' + @JobCodes + ')
    ) p'

EXEC sp_executesql @Sql

Dynamic Pivot Table across multiple columns

2 Answers2