SQL pivot unpivot query

Question

I don't have much experience with pivot/unpivot and could use some help. I have a SQL query with data as :

Category Account   Name           Value
001      1234     BALANCE_01      800
001      1234     BALANCE_02      1000
001      1234     BALANCE_03      1500
001      4567     BALANCE_01      900
001      4567     BALANCE_02      1200
001      4567     BALANCE_03      800

I need it to appear as:

Category Account   BALANCE_01  BALANCE_02  BALANCE_03
001       1234       800         1000         1500
001       4567       900         1200         800

How do I do this?

Thanks, Marcie

Answer 1

One way is to do this is by using conditional aggregation:

SELECT Category,
       Account,
       MAX(CASE WHEN Name = 'BALANCE_01' THEN Value ELSE NULL END) AS BALANCE_01,
       MAX(CASE WHEN Name = 'BALANCE_02' THEN Value ELSE NULL END) AS BALANCE_02,
       MAX(CASE WHEN Name = 'BALANCE_03' THEN Value ELSE NULL END) AS BALANCE_03
FROM Table
GROUP BY Category, Account