Pivot / Crosstab query in Oracle 10g (dynamic column number)

Question

I have this kind of table

UserName      Product     NumberPurchaces
--------      -------     ---------------
'John Doe'    'Chair'     4
'John Doe'    'Table'     1
'Jane Doe'    'Table'     2
'Jane Doe'    'Bed'       1

How to create a query that will provide this summary view in Oracle 10g?

 UserName   Chair   Table   Bed
 --------   -----   -----   ---
 John Doe   4       1       0
 Jane Doe   0       2       1

Any way to do this dynamically? I have seen so many approaches (decoding, PL / SQL loops, unions, 11 gigabytes)

But I have yet to find something that will work for me based on the above example.

---

Edit : I do not know the quantity or type of products during development, so this should be dynamic

Answer 1

Oracle 11g is the first to support PIVOT / UNPIVOT, so you should use:

  SELECT t.username,
         MAX(CASE WHEN t.product = 'Chair' THEN t.numberpurchases ELSE NULL END) AS chair,
         MAX(CASE WHEN t.product = 'Table' THEN t.numberpurchases ELSE NULL END) AS tbl,
         MAX(CASE WHEN t.product = 'Bed' THEN t.numberpurchases ELSE NULL END) AS bed
    FROM TABLE t
GROUP BY t.username

DECODE, CASE 9i.

Answer 2

, . MAX() , "CHAIR", "TABLE" ..

, , . .