Organizational Research By

Surprising Reserch Topic

pivot crosstab query in oracle 10g dynamic column number


pivot crosstab query in oracle 10g dynamic column number  using -'sql,oracle,pivot,transformation'

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
    

asked Oct 7, 2015 by okesh.badhiye
0 votes
20 views



Related Hot Questions

2 Answers

0 votes

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.

answered Oct 7, 2015 by maurya
0 votes

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.

answered Oct 7, 2015 by yogeshplv

...