De-Pivoting Data (?)

[ThomVF]

Hi all - I need to do what I refer to as de-pivoting, or also may be called "de-normalizing".

Here is a sample set of records...

Customer Month Count
1 1 120
1 2 230
1 3 89
2 1 102
2 2 702
2 3 345
...

I need to be able to query this and get one row per Customer, with values for each of the months.
Can I do this without a complex PL/SQL procedure?

Desired Results:

Customer Month1 Month2 Month3 ...
1 120 230 89
2 102 702 345

Thanks very much.

Customer
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[jee]

If you know the max number of months (for instance 12, if the table covers one year), you can do something like this:

Select 
  from (select customer, count
          from table_name
         where month = month1) m1,
       (select customer, count
          from table_name
         where month = month2) m2,
       (select customer, count
          from table_name
         where month = month3) m3,
       .
       .
       .
       (select customer, count
          from table_name
         where month = month12) m12
  where m2.customer = m1.customer
    and m3.customer = m2.customer
    .
    .
    .
    and m12.customer = m11.customer
[\code]

If the max number of months is undefined, you will probably have to use dynamic SQL within a PL/SQL procedure.

 

[jee]

Whoops, the top line of the select should have been:

select m1.customer, m1.count MONTH1, m2.count MONTH2, ... m12.count MONTH12

 

[stefanhei]

Hi.
You could also use DECODE:
SELECT customer,
SUM(DECODE(month,1,count,null)) month1,
SUM(DECODE(month,2,count,null)) month2,...
FROM table_name
GROUP BY customer

This would guarantee you that there realy is only 1 column per customer. Of course only you have know the max number of months as in jees solution...

Stefan