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

This link may prove as some assistance: https://www.techonthenet.com/oracle/pivot.php — Taku_, May 22 '17 at 14:22
[Oracle](https://stackoverflow.com/questions/20551262/oracle-sql-pivot-table) - [Sql-Server](https://stackoverflow.com/a/15745076/5817001) - [MySql](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — fqhv, May 22 '17 at 14:31
What is expected result, if your table also have another row with values: `001, 4567, BALANCE_02, 1300` ? — Oto Shavadze, May 22 '17 at 14:33

score 0 · Accepted Answer · answered May 22 '17 at 14:27

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

score 0 · Answer 2 · answered May 22 '17 at 14:27

I would just just a group by

SELECT Category, Account, 
       SUM(CASE WHEN NAME='BALANCE_1' THEN Value ELSE 0 END) AS BALANCE_1,
       SUM(CASE WHEN NAME='BALANCE_2' THEN Value ELSE 0 END) AS BALANCE_2,
       SUM(CASE WHEN NAME='BALANCE_3' THEN Value ELSE 0 END) AS BALANCE_3
FROM Your_Table_You_Did_Not_Name
GROUP BY Category, Account

Note, if you have more than one row with the same Category, Account and Name this will fail -- but you don't tell us how to handle that.

SQL pivot unpivot query

2 Answers2