CODEWITHSUNDEEP
sqloracle-databaseoracle10gpivot
mahen
mahen

Reputation: 213

Rows to columns

I have a table like

cases    open/close    count 
-----------------------------
A        open          10       
A        close         15
B        open          6
B        close         4

I need to get a result table that is like

cases   total     open     close  
---------------------------------
A       25        10       15
B       10        6        4

Any ideas about this?

Upvotes: 2

Views: 370

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332531

This is commonly referred to as a pivot query, converting row into columnar data. Use:

  SELECT t.cases,
         SUM(t.count) AS total,
         SUM(CASE WHEN t.open_close = 'open' THEN t.count ELSE 0 END) AS open,
         SUM(CASE WHEN t.open_close = 'close' THEN t.count ELSE 0 END) AS close
    FROM YOUR_TABLE t
GROUP BY t.cases

Oracle didn't add the ANSI PIVOT (and UNPIVOT) syntax until 11g.

Upvotes: 4

pavanred
pavanred

Reputation: 13793

You can use pivot over the column cases and aggregate sum of count.

Upvotes: 0

Related Questions