Re: Need help on a query!!!

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 09 Oct 2002 13:11:34 +0200
Message-ID: <ao12vm$334$1@ctb-nnrp2.saix.net>

Jackie wrote:

> USERID LOGIN_DATE
> --------- ------------------
> 100200 10-11-2002
> 100200 09-08-2002
> 100200 10-11-2002

<snipped>

> USERID LOGIN_DATE1 LOGIN_DATE2 LOGIN_DATE3
> --------- ----------- ----------- -----------
> 100200 10-11-2002 09-08-2002 10-11-2002
> 400300 05-06-2002 05-06-2002
> 500200 01-01-2002 02-02-2001
> 600100 01-08-2002

Not possible with SQL if you have a variable number of LOGIN_DATE columns, i.e. LOGIN_DATE1 to LOGIN_DATEn

If you do have a fixed number of columns you want in the pivot table/result set, there are a couple of ways.

You can create an intermediate table that enables you to perform the pivoting. Something like

table :PIVOT_QUARTER
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

You can then use this table, to turn something like this: YEAR QUARTER SALES
----- -------- -------

1999   1         200000
1999   2         250000
1999   3         245000
1999   4         275000
2000   1         270000
2000   2         275000
2000   3         300000
2000   4         305000

into this:
YEAR QUATER_1 QUARTER_2 QUARTER_3 QUARTER_4 ---- --------- ---------- --------- ----------

1999  200000    250000     245000     275000
2000  270000    275000     300000     305000

using this:
SELECT
s.year,

  SUM( s.sales * q1 )  QUARTER_1,
  SUM( s.sales * q2 )  QUARTER_2,
  SUM( s.sales * q3 )  QUARTER_3,
  SUM( s.sales * q4 )  QUARTER_4
FROM sales s, 
     pivot_quarter p

WHERE s.quarter = p.quarter
GROUP BY s.year

The same can also be achieved via non-ANSI SQL in Oracle by using the DECODE statement (in which case you do not need the intermediate join table).

However - it is not possible to dynamically at runtime create a variant number of columns in the result set of a SQL SELECT.

To do this, you need to write some supporting code that will populate a dynamic array for you and then print it to file.

--
Billy

Received on Wed Oct 09 2002 - 06:11:34 CDT