title: Dynamic Pivot Table across multiple columns
body: <p>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.</p>
  
  <p>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 <a href="http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/" rel="nofollow noreferrer">this</a> to build it.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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?</p>
  
  <p>My thought was to try and repeat the step 16 times but I don't think this is even close to the right way.</p>
  
  <pre><code>DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
  DECLARE @ColumnName AS NVARCHAR(MAX)
  DECLARE @N AS INT
  DECLARE @NCHAR AS NVARCHAR(MAX)
  SET @N = 1
  
  WHILE @N &lt; 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
  </code></pre>
  
  <p>Original</p>
  
  <pre><code>+-------------+----------+----------+----------+-----------+-----------+-----------+
  | TimesheetID | JobCode1 | JobCode2 | JobCode3 | TotalJob1 | TotalJob2 | TotalJob3 |
  +-------------+----------+----------+----------+-----------+-----------+-----------+
  | 1           | J1       | J3       |          | 10        | 9         |           |
  +-------------+----------+----------+----------+-----------+-----------+-----------+
  | 2           | J2       | J1       | J3       | 5         | 5         | 5         |
  +-------------+----------+----------+----------+-----------+-----------+-----------+
  | 3           | J2       |          |          | 6         | 3         | 1         |
  +-------------+----------+----------+----------+-----------+-----------+-----------+
  </code></pre>
  
  <p>What I want to achieve</p>
  
  <pre><code>+-------------+----+----+----+----+----+
  | TimesheetID | J1 | J2 | J3 | J4 | J6 |
  +-------------+----+----+----+----+----+
  | 1           | 10 |    | 9  |    |    |
  +-------------+----+----+----+----+----+
  | 2           | 5  | 5  | 5  |    |    |
  +-------------+----+----+----+----+----+
  | 3           |    | 6  |    | 3  | 1  |
  +-------------+----+----+----+----+----+
  </code></pre>