title: SQL Server 2008 : pivoting on multiple columns
body: <p>Need help with the pivot clause in SQL Server 2008. I have a table with this info: </p>
  
  <p>I have a table with 9 columns: <code>ID</code>, <code>Period_1</code> to <code>Period_4</code> as dates (i.e. 2013-04, 2013-07, etc.) and <code>Amount_1</code> to <code>Amount_4</code> (i.e. 30, 40, etc.). I want all the distinct dates from <code>Period_1</code> to <code>Period_4</code> as column names and then pivot <code>Amount_1</code> corresponding to <code>Period_1</code>, <code>Amount_2</code> corresponding to <code>Period_2</code>, <code>Amount_3</code> corresponding to <code>Period_3</code> and <code>Amount_4</code> corresponding to <code>Period_4</code> as row values. </p>
  
  <p>This is my T-SQL that I came up with for the moment:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX)
  ,@query AS NVARCHAR(MAX)
  
  SELECT @cols = STUFF((
          SELECT DISTINCT ',' + QUOTENAME(ans)
          FROM (
              SELECT Period_1 AS ans
              FROM Booking
  
              UNION
  
              SELECT Period_2 AS ans
              FROM Booking
  
              UNION
  
              SELECT Period_3 AS ans
              FROM Booking
  
              UNION
  
              SELECT Period_4 AS ans
              FROM Booking
              ) a
          FOR XML PATH('')
              ,TYPE
          ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
  
  SET @query = 'SELECT Id ' + @cols + ' from 
           (
              select Id, Period_1, Amount_1, Period_2, Amount_2
              from Booking
           ) x
          pivot 
          (
              max(Amount_1)
              for Period_1 in (' + @cols + ')
  
          ) p 
  (
              max(Amount_2)
              for Period_2 in (' + @cols + ')
  
          ) p
  
          '
  
  EXECUTE (@query)
  </code></pre>
  
  <p>and I get the ERROR:</p>
  
  <blockquote>
    <p>Msg 265, Level 16, State 1, Line 18<br>
    The column name "2013-10   " specified in the PIVOT operator conflicts with the  existing column name in the PIVOT argument.</p>
  </blockquote>
  
  <p>Is there any way to do a pivot query on multiple columns containing same values? Please write respond with an example on how to do it. </p>
  
  <p>I appreciate any kind of help on this. Thanks in advance. </p>