3

I have this table view

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

How can I create a query that will provide this pivot view in Oracle 10g ?

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

Any way to do it dynamically? I saw so many approaches (decode, PL/SQL loops, unions, 11g pivot)

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

Edit: I don't know the number or type of products in development time so this has to be dynamic

Eran Medan
  • 44,555
  • 61
  • 184
  • 276
  • You can't. Any particular SQL statement has to return a resultset with the same number of columns, name of columns and datatypes of columns – Gary Myers Aug 13 '10 at 23:38

2 Answers2

4

Oracle 11g is the first to support PIVOT/UNPIVOT, so you have to 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

You could use DECODE, but CASE has been supported since 9i.

tckmn
  • 57,719
  • 27
  • 114
  • 156
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 2
    Thanks!, How can this become dynamic now? e.g. not knowing up front what is the products set (dynamic list that is not known in design time) – Eran Medan Aug 13 '10 at 18:49
3

I guess one would have to write some code to dynamically create the query. Each MAX() line is identical except for the 'CHAIR', 'TABLE', etc, strings.

So, one would have to itterate through the data to find all the products and build up a second query as one goes. Then execute that dynamically built query.

MatBailie
  • 83,401
  • 18
  • 103
  • 137