title: Pivot sql convert rows to columns
body: <p>The below query get the following out put.</p>
  
  <pre><code>Service Date  Product1 Product2 
  01/Jun/2015    1           3
  02/Jun/2015    2           5
  </code></pre>
  
  <p>Instead of that i want the date to be in the columns so out put should be like this.</p>
  
  <pre><code>Products  01/Jun/2015  02/Jun/2015 
  Product1    1           3
  Product2    2           5
  </code></pre>
  
  <p><strong>Query</strong></p>
  
  <pre><code>DECLARE @cols AS nvarchar(max),
          @query AS nvarchar(max)
  SELECT
    @cols = STUFF((SELECT
      ',' + QUOTENAME(Product_Name)
    FROM dbo.Store where CatID='2'
  
    GROUP BY Product_Name
    ORDER BY Product_Name
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 1, '')
  SET @query = 'SELECT Replace(CONVERT(NVARCHAR, Service_Date, 106), '' '', ''/'') AS [Service Date],' + @cols + ' from ( select Service.Service_Date, Store.Product_Name, Servicelist.ProductQty FROM   dbo.Service INNER JOIN dbo.Servicelist ON dbo.Service.Service_ID = dbo.Servicelist.Service_ID INNER JOIN dbo.Store ON dbo.Servicelist.Pro_ID = dbo.Store.Pro_ID) x pivot ( SUM(ProductQty) for Product_Name in (' + @cols + ') ) p '
  EXECUTE (@query);
  </code></pre>