Re: reverse pivoting

"Kenny Yu" <kyu_at_biodiscovery.com> wrote:

>Is there systematic approaches in data warehousing to do the following
>converting table Pivot (customer, product, amount) -- many rows per
>customer into table RPivot (customer, prod1Amt, prod2Amt, ... prodnAmt)
> -- one row per customer

Yes.

>I am interested in a pure SQL way.

What is pure SQL?

And why is this that I have answered this same question a few times a year in this newsgroup since '97... ;-)

Here's a copy and paste of an old posting (dug up in groups.google) on the topic I made:

--
The trick is to create a pivot table, and join the data set with that.
The result set will then be pivoted or cross-tabbed, i.e. you make
columns into rows and rows into columns.

This is best explained with a little example.

YEARLY_RESULTS:
Year Quarter Value
---- ------- -----
94   1       100
94   2       150
94   3       170
94   4       200
95   1       190
95   2       210
95   3       205
95   4       215

OK, you want the data of the above table to be displayed (via a SQL
SELECT statement) as follows:

Year Q1  Q2  Q3  Q4
---- --- --- --- ---
94   100 150 170 200
95   190 210 205 215


How to do it? First you need to create (what I call) a pivot table. We
want to pivot the QUARTER column and for a quarter  have 4 distinct
values. So we create the following table:

PIVOT_TABLE:
Quarter Q1  Q2  Q3  Q4
------- --  --  --  --
1       1   0   0   0
2       0   1   0   0
3       0   0   1   0
4       0   0   0   1

For Quarter One, only Q1 is "valid" and the others are "invalid". Thus
we mark the Q1 column for Quarter 1 with a one and the other columns
with zeros. Ditto for the other columns (reminiscent of bitmasking).

OK, now we join our data set with the pivot table.
SELECT
  yearly_results.year,
  SUM( yearly_results.value * pivot_table.q1) "Q1",
  SUM( yearly_results.value * pivot_table.q2) "Q2",
  SUM( yearly_results.value * pivot_table.q3) "Q3",
  SUM( yearly_results.value * pivot_table.q4) "Q4"
FROM yearly_results,
     pivot_table
WHERE yearly_results.quarter = pivot_table.quarter
GROUP BY
  yearly_results.year


The concept here is that we create 4 brand new columns - one for every
quarter. Then we sum the values from the yearly results table for each
of these 4 columns. However, we do not want to add quarter 1's value
into the wrong columns. Enter the pivot table - it causes quarter 1's
value to be multiplied with zero for any other quarter except quarter
1.
--

The above is the "purest" SQL way - but it needs the creation of an
additional table.

There are other methods too. 

Oracle supports the DECODE statement (which gives you IF-THEN-ELSE
capability in the SELECT statement). This statement can be used in
place of the pivot table.
SELECT
  year,
  SUM( DECODE( quarter, 1, value, 0) ) "Q1",
  SUM( DECODE( quarter, 2, value, 0) ) "Q2",
  SUM( DECODE( quarter, 3, value, 0) ) "Q3",
  SUM( DECODE( quarter, 4, value, 0) ) "Q4"
FROM yearly_results
GROUP BY year

This is nice and clean, however is not "standard SQL" (whatever that
may be).

You can do multiple cartesian joins on the the same table. Using the
above example:
SELECT
  q1.year,
  SUM( q1.value ) "Q1",
  SUM( q2.value ) "Q2",
  SUM( q3.value ) "Q3",
  SUM( q4.value ) "Q4",
FROM yearly_results q1,
     yearly_results q2,
     yearly_results q3,
     yearly_results q4
WHERE q1.quarter = 1
AND   q2.quarter = 2
AND   q3.quarter = 3
AND   q4.quarter = 4
AND   q1.year = q2.year
AND   q1.year = q3.year
AND   q1.year = q4.year
GROUP BY q1.year

Damn, don't you just love SQL! :-)

I have yet to find a query that can not be done in SQL.. 
--
Billy