Search code examples
sqlsql-serverpivotconditional-aggregation

SQL Pivot multiple column

I have table like this:-

enter image description here

and want output like this:-

enter image description here

Any help?

Solution

  • Below is a more standard/general approach (conditional aggregation) for this problem that would work across multiple Databases (including SQL Server).

    SELECT
 ID
,MAX(CASE WHEN DOMAIN = 'A' THEN SCORE END) AS DOMAIN_A
,MAX(CASE WHEN DOMAIN = 'A' THEN BAND  END) AS BAND_A
,MAX(CASE WHEN DOMAIN = 'B' THEN SCORE END) AS DOMAIN_B
,MAX(CASE WHEN DOMAIN = 'B' THEN BAND  END) AS BAND_B
,MAX(CASE WHEN DOMAIN = 'C' THEN SCORE END) AS DOMAIN_C
,MAX(CASE WHEN DOMAIN = 'C' THEN BAND  END) AS BAND_C
,MAX(CASE WHEN DOMAIN = 'D' THEN SCORE END) AS DOMAIN_D
,MAX(CASE WHEN DOMAIN = 'D' THEN BAND  END) AS BAND_D
,MAX(CASE WHEN DOMAIN = 'E' THEN SCORE END) AS DOMAIN_E
,MAX(CASE WHEN DOMAIN = 'E' THEN BAND  END) AS BAND_E
FROM MyTable
Group by ID
;

    Note : If you are not using T-SQL (SQL Server), Pivot function won't work as it is specific to SQL Server