Re: SQL Question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 19 Jan 2000 10:48:43 +0100
Message-ID: <8641a6$897$1_at_news3.isdnet.net>

[Quoted] May be something like that:

v734> create table t (start_date date,
  2                  month1_data number, month2_data number,
  3                  month3_data number, month4_data number,
  4                  month5_data number, month6_data number);

Table created.

v734> insert into t values (to_date('01/01/2000','DD/MM/YYYY'),

  2                        1000,1500,1200,1900,1400,900);
v734> insert into t values (to_date('01/02/2000','DD/MM/YYYY'),
  2                        200,400,300,600,300,100);
v734> insert into t values (to_date('01/03/2000','DD/MM/YYYY'),
  2                        1100,1700,1600,1200,1000,700);

v734> commit;

Commit complete.

v734> select '01-JAN-2000' "Start date",

  2         decode(trunc(months_between(start_date,
  3                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]   4                0, month1_data, 0) "Month 1",
  5         decode(trunc(months_between(start_date,
  6                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]   7                0, month2_data, 1, month1_data, 0) "Month 2",
  8         decode(trunc(months_between(start_date,
  9                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]  10                0, month3_data, 1, month2_data, 2, month1_data, 0) "Month 3",
 11         decode(trunc(months_between(start_date,
 12                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]  13                0, month4_data, 1, month3_data, 2, month2_data, 3,
month1_data,
 14                0) "Month 4",
 15         decode(trunc(months_between(start_date,
 16                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]  17                0, month5_data, 1, month4_data, 2, month3_data, 3,
month2_data,
 18                4, month1_data, 0) "Month 5",
 19         decode(trunc(months_between(start_date,
 20                                     to_date('01/01/2000','DD/MM/YYYY'))),
[Quoted]  21                0, month6_data, 1, month5_data, 2, month4_data, 3,
month3_data,
 22                4, month2_data, 5, month1_data, 0) "Month 6"

 23 from t
 24 where start_date >= to_date('01/01/2000','DD/MM/YYYY')  25 order by start_date
 26 /

Start date Month 1 Month 2 Month 3 Month 4 Month 5 Month 6

----------- ---------- ---------- ---------- ---------- ---------- ----------
[Quoted] [Quoted] [Quoted] 01-JAN-2000       1000       1500       1200       1900       1400        900
[Quoted] 01-JAN-2000          0        200        400        300        600        300
[Quoted] 01-JAN-2000          0          0       1100       1700       1600       1200

[Quoted] 3 rows selected.

--
Have a nice day
Michel


Craig Grell <craigg_at_thewebmarket.com> a écrit dans le message :
3884d930_2_at_news.cadvision.com...


> I have a table with the following structure.


>

> start date, month1_data, month2_data, month3_data, month4_data, month5_data,

> month6_data

>

> with the following information

>

> '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900

> '01-FEB-2000', 200, 400, 300, 600, 300, 100

> '01-MAR-2000', 1100, 1700, 1600, 1200, 1000, 700

>

>

> How can I select this information to make the monthly data align so that the

> result of the select would look like the following when aligned on the date

> '01-JAN-2000'

>

> '01-JAN-2000', 1000, 1500, 1200, 1900, 1400, 900

> '01-JAN-2000', 0, 200, 400, 300, 600, 300

> '01-JAN-2000', 0, 0, 1100, 1700, 1600, 1200

>

>

>

> Thank you for any suggestions

>

> Craig

>

>

Received on Wed Jan 19 2000 - 10:48:43 CET