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开发者_Python百科

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

Any ideas about this?

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.

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