Re: reverse pivoting -- more complex situation

I really object to you having us do your homework for you.

But then that comes from teaching myself.

If I found one of my students doing what you are doing likely they would be most unhappy.

It is one thing to ask for guidance. Quite another to ask for the answer.

Daniel Morgan

Kenny Yu wrote:

> 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 - 12:12:31 CST