Re: [HELP]How to Create Cross Table!!
Paul
I think I may have misunderstood your question ('twas before my early morning
nicotine/caffeine combo buzz :-)). Here is a solution that may probably be what
you are looking for:
1) create a stored procedure for sales which takes argument (year, month) and
returns the sales value.
create procedure sales (i_year integer, i_month integer) returning integer; define v_sales integer;
select sales into v_sales from tablename where month = i_month and year =
i_year;
if (v_sales is null) then
let v_sales = 0;
end if;
return v_sales;
end procedure;
2) execute this sql:
select year, sales(year, 1), sales(year, 2), ..., sales(year, 12)
from tablename
order by year;
HTH
Sujit
---------------------- Forwarded by Sujit Pal on 09/16/99 09:16 AM
---------------------------
From: Sujit Pal on 09/16/99 08:53 AM
To: paul <paoching@panpi.com.tw>
cc: informix-list@iiug.org
Subject: Re: [HELP]How to Create Cross Table!! (Document link: Database 'Sujit
Pal', View '($Sent)')
Paul
Use a stored procedure to calculate the values for each month and return 0's in
the other months, or use a union, like so:
select sales, month, [null, ... 11 times] from table where month = "jan" andyear = 1999
union
select sales, null, month, [null, ... 10 times] from table where month = "feb"
and year = 1999
union...
select sales, [null,...11 times], month from table where month = "dec" and year= 1999;
HTH
Sujit
paul <paoching@panpi.com.tw> on 09/16/99 03:23:57 AM
Please respond to paul <paoching@panpi.com.tw>
To: informix-list@iiug.org
cc: (bcc: Sujit Pal)
Subject: Re: [HELP]How to Create Cross Table!!
Dear sir
I want to use one field's value as result's column!
original table:
Sales Year Month Money
query result:
Salse, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12
----------------------------------------------------------------------------
---
Paul 1 1 1
................................................................
Please Help, I have to finish this report tonight!
Robert Taylor <robertt@scotlegal.com> wrote in message
news:7rqfso$d22$1@uranium.btinternet.com...
> Sorry , I don't understand the question.
>
> paul <paoching@panpi.com.tw> wrote in message
> news:7rqaub$70s@netnews.hinet.net...
> > Dear all
> > What's the SQL syntax of Informix can I create cross table result!
> > Thanks in advance!
> >
> >
> >
>
>
thank you sir!
<Sujit.Pal@bankofamerica.com> wrote in message
news:7rr6r2$bk$1@news.xmission.com...
>
>
>
> Paul
>
> I think I may have misunderstood your question ('twas before my early
morning
> nicotine/caffeine combo buzz :-)). Here is a solution that may probably be
what
> you are looking for:
>
> 1) create a stored procedure for sales which takes argument (year, month)
and
> returns the sales value.
> create procedure sales (i_year integer, i_month integer) returninginteger;
> define v_sales integer;
> select sales into v_sales from tablename where month = i_month and
year =
> i_year;
> if (v_sales is null) then
> let v_sales = 0;
> end if;
> return v_sales;
> end procedure;
>
> 2) execute this sql:
> select year, sales(year, 1), sales(year, 2), ..., sales(year, 12)
> from tablename
> order by year;>
> HTH
> Sujit
>
> ---------------------- Forwarded by Sujit Pal on 09/16/99 09:16 AM
> ---------------------------
>
> From: Sujit Pal on 09/16/99 08:53 AM
>
> To: paul <paoching@panpi.com.tw>
> cc: informix-list@iiug.org
> Subject: Re: [HELP]How to Create Cross Table!! (Document link: Database
'Sujit
> Pal', View '($Sent)')
>
> Paul
>
> Use a stored procedure to calculate the values for each month and return
0's in
> the other months, or use a union, like so:
>
> select sales, month, [null, ... 11 times] from table where month = "jan"
and> year = 1999
> union
> select sales, null, month, [null, ... 10 times] from table where month ="feb"
> and year = 1999
> union
> ...
> select sales, [null,...11 times], month from table where month = "dec" andyear
> = 1999;
>
> HTH
> Sujit
>
>
>
>
> paul <paoching@panpi.com.tw> on 09/16/99 03:23:57 AM
>
> Please respond to paul <paoching@panpi.com.tw>
>
> To: informix-list@iiug.org
> cc: (bcc: Sujit Pal)
> Subject: Re: [HELP]How to Create Cross Table!!
>
>
>
> Dear sir
> I want to use one field's value as result's column!
>
> original table:
> Sales Year Month Money
>
> query result:
> Salse, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12
> --------------------------------------------------------------------------
--
> ---
> Paul 1 1 1
> ................................................................
>
> Please Help, I have to finish this report tonight!
>
>
> Robert Taylor <robertt@scotlegal.com> wrote in message
> news:7rqfso$d22$1@uranium.btinternet.com...
> > Sorry , I don't understand the question.
> >
> > paul <paoching@panpi.com.tw> wrote in message
> > news:7rqaub$70s@netnews.hinet.net...
> > > Dear all
> > > What's the SQL syntax of Informix can I create cross table result!
> > > Thanks in advance!
> > >
> > >
> > >
> >
> >
>
>
>
>
>
>
>
>
>
>
>
>