Dis aggregation in SQL

Question

I have a table

Team           Matches       Won
A                10           2
B                20           4 
C                40           10

I want to convert this table into following

Team             Won/Lost         Won Lost Number
A                    Won              2
A                    Lost             8
B                    Won              4
B                    Lost             16
C                    Won              10
C                   Lost              30

Thanks in advance !

Answer 1

In fact, there is no aggregation here - just a simple subtraction. I'd do this in two queries, one for wins and one for loses, and combine them with the union all operator:

SELECT    team, 'Won' AS "Won/Lost", won AS "Won/Lost Number"
FROM      my_table
UNION ALL
SELECT    team, 'Lost', matches - won
FROM      my_table
ORDER BY  1 ASC, 2 DESC