SQL Pivot multiple column

Question

I have table like this:-

and want output like this:-

Any help?

Answer 1

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