Re: SQL Question

Craig
[Quoted]   If you get desparate enough, you might try this:

ACCEPT SDATE CHAR PROMPT 'Start Date: '

SELECT &SDATE, month1_data, month2_data, month3_data, month4_data, month5_data, month6_data
FROM your_table
WHERE start_date = TO_DATE(&SDATE,'DD-MON-YYYY') UNION
SELECT &SDATE, ' 0', month2_data, month3_data, month4_data, month5_data, month6_data
FROM your_table
WHERE start_date = ADD_MONTHS(TO_DATE(&SDATE,'DD-MON-YYYY'),1) UNION
SELECT &SDATE, ' 0',' 0', month3_data, month4_data, month5_data, month6_data
FROM your_table
WHERE start_date = ADD_MONTHS(TO_DATE(&SDATE,'DD-MON-YYYY'),2) UNION
-- think you get the idea by now, just keep on adding unions.

Assuming MONTHn_DATA is a number, you may want to LPAD(TO_CHAR (MONTHn_DATA),4) (or whatever the max number of digits could be) all of the MONTHn_DATA columns to maintain alignment.

It's an painfully ugly, brute force solution - but your requirements that all output lines display a single date, rather than the record date and that months after January display progressively fewer columns limit the alternatives.

Hope this helps,

Paul

In article <3884d930_2_at_news.cadvision.com>,   "Craig Grell" <craigg_at_thewebmarket.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 19 2000 - 03:05:56 CET