Re: SQL for all months in range, data or not

consider using decode

select decode(to_char(transaction_date,'MM'), '01',data1) JAN,

            decode(to_char(transaction_date,'MM'), '02',data1) FEB,
            etc.

"Eischeid" <tge_at_fastrus.com> wrote in message news:8a14d259.0107251554.6547932c_at_posting.google.com...
> Hi,
>
> Need to get column headers in SQL for months regardless of whether
> data exists.
>
> DB is public school data system for large city - 55 schools. Columns
> are Sep through Jun, taken from transaction date (using Oracle
> function) for a student. Rows are various categories. Body of table
> is student count for that school and category by month but there is
> not necessarily a student count for every category for every school
> every month. However, school board wants all tables to line up
> visually vertically, even if no data. Result table being used by
> another tool (Business Objects) for display.
>
> Thought of using in line table, e.g.
> SELECT
> data1,
> data2,
> etc.
> FROM
> table1,
> table2,
> (Select 'Jan','Feb','Mar', etc from SYS.DUAL) etc.
> WHERE
> table1.school_year = 'YY', -- where YY is school year,
> -- e.g. Sep 2000 - Jun 2001, YY = '00'
> ???
>
> What do I need here? I understand outer joins but how do I outer join
> an in line table?
>
> Note that the selection for school year is NOT based on the
> transaction date. school_year is column in source table. All that is
> necessary is that the months taken from transaction date can be
> matched to the in line table, or whatever is used.
>
> Any ideas? I am not SQL guru, so this may seem simple to some.
>
> Thanks.
>
> Tom Eischeid
Received on Fri Jul 27 2001 - 21:42:12 CEST