title: How to transpose rows to columns in SQL or SSIS having dynamic values
body: <p>I have following table where daily data is saved:</p>
  
  <p><img src="https://i.stack.imgur.com/htROo.png" alt="Data generated by SQL Query"></p>
  
  <p>I need to read the last 7 days of data and transpose the rows to columns to save it in Excel Report. I am trying to automate this with SSIS. 
  Note that here the dates which will behave as columns after transpose will be dynamic based on last seven days so I cannot use static column names in pivot function.
  The desired output will look like this:</p>
  
  <p><img src="https://i.stack.imgur.com/RxcYV.png" alt="Desired output format"></p>
  
  <p>Please advice how can I accomplish the output either through MS-SQL or SSIS. Thanks in advance. Following is table schema and sample data:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#PriorityTrends') IS NOT NULL DROP TABLE #PriorityTrends  
  
  CREATE TABLE #PriorityTrends (
      CountDate DATETIME,
      [Priority 1] INT,
      [Priority 2] INT,
      [Priority 3] INT,
      [Priority 4] INT,
      [Priority 5] INT,
      Total INT
      )
  
  INSERT INTO #PriorityTrends
  VALUES ('6/24/2014', 163, 235, 741, 265, 1932, 1404)
  
  INSERT INTO #PriorityTrends
  VALUES ('6/25/2014', 174, 368, 775, 256, 2634, 1573)
  
  INSERT INTO #PriorityTrends
  VALUES ('6/26/2014', 201, 448, 703, 270, 2717, 1622)
  
  INSERT INTO #PriorityTrends
  VALUES ('6/27/2014', 140, 464, 612, 257, 1776, 1473)
  
  INSERT INTO #PriorityTrends
  VALUES ('6/30/2014', 106, 333, 1274, 311, 1480, 2024)
  
  INSERT INTO #PriorityTrends
  VALUES ('7/1/2014', 164, 834, 1533, 1145, 10483, 3676)
  
  INSERT INTO #PriorityTrends
  VALUES ('7/2/2014', 136, 378, 1421, 391, 9018, 2326)
  </code></pre>