Search code examples
sqlsql-serversql-server-2000

Pivot the columns in SQL Server

I have a SQL Server table with the structure as shown in the 1st picture. This has to be displayed as shown in the 2nd picture. How to achieve this in SQL Server 2000?

enter image description here

Solution

  • Given that same pattern for all records (_A1, _A2) something like the following should work for you: add in the extra columns and/or take out the unnecessary ones as you wish. 

    SELECT 
  LEFT(measure, CHARINDEX('_', measure)-1) AS Measure,
  LEFT(measure, CHARINDEX('_', measure)-1) + '_A1' AS Measure_A1, --Not sure you need this
  SUM(CASE WHEN RIGHT(measure,2) = 'A1' THEN YTD ELSE 0 END) AS YTD_A1,
  SUM(CASE WHEN RIGHT(measure,2) = 'A1' THEN Q1 ELSE 0 END) AS Q1_A1,
  SUM(CASE WHEN RIGHT(measure,2) = 'A1' THEN Q2 ELSE 0 END) AS Q2_A1,
  LEFT(measure, CHARINDEX('_', measure)-1) + '_A2' AS Measure_A2, --Not sure you need this
  SUM(CASE WHEN RIGHT(measure,2) = 'A2' THEN YTD ELSE 0 END) AS YTD_A2,
  SUM(CASE WHEN RIGHT(measure,2) = 'A2' THEN Q1 ELSE 0 END) AS Q1_A2,
  SUM(CASE WHEN RIGHT(measure,2) = 'A2' THEN Q2 ELSE 0 END) AS Q2_A2
FROM Structure
GROUP BY LEFT(measure, CHARINDEX('_', measure)-1)

    SQL Fiddle