Re: reverse pivoting -- more complex situation

Thanks, Bill, for the comprehensive presentation of the techniques.

I need to go further from here. By 'pure sql', I meant anything done with a sql statement, without a program to dynamically creating a sql statement. All your code is pure in this sense.

A more complex situation, --and this is not a homework assignment, -- is where the number of quarters are not known, or arbitrarily defined for a year. More realistically, I am dealing with a security mechanism in which one can create security policies (years, in your example). Each policy has a collections of READ and WRITE groups (quarters). For example:

policy_id group_id Access

1                 1              READ
2                 1              WRITE
3                 2               READ

I want to expand it into
policy_id group_1 group_2

1                READ   WRITE

Therefore, the number of columns in the new table is unknown since you can create new groups.

I am ready to work in PL/SQL to generate a dynamic SQL to create a view. I generally believe that everything can be done in one single statement, but am challenged here.

Kenny

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:3c8d97ac.61762883_at_news.saix.net...
> "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
>
>
>
Received on Tue Mar 12 2002 - 11:16:13 CST