[HELP]How to Create Cross Table!!
Dear all What's the SQL syntax of Informix can I create cross table result! Thanks in advance!
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! > > >
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! > > > > > > > >
This can be done with a single select using the case statement:
select person, year,
sum(case when month = 1 then money else 0 end) M1,
sum(case when month = 2 then money else 0 end) M2,...
sum(case when month = 12 then money else 0 end) M12
from sales_table
group by person, year
Jay Buckler
paul <paoching@panpi.com.tw> wrote in message
news:7rqgbk$mmh@netnews.hinet.net...
> 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!
> > >
> > >
> > >
> >
> >
>
>
thanks!
Where can I found the statement about this?
I found all the Informix Reference Books (sql syntax, sql tour ....), but
got nothing!
thanks again!
Jay Buckler <buckler@sover.net> wrote in message
news:6wdE3.15040$N77.1119408@typ11.nn.bcandid.com...
> This can be done with a single select using the case statement:
>
> select person, year,
> sum(case when month = 1 then money else 0 end) M1,
> sum(case when month = 2 then money else 0 end) M2,> ...
> sum(case when month = 12 then money else 0 end) M12
> from sales_table
> group by person, year
>
> Jay Buckler
>
> paul <paoching@panpi.com.tw> wrote in message
> news:7rqgbk$mmh@netnews.hinet.net...
> > 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!
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
paul wrote:
>
> thanks!
> Where can I found the statement about this?
> I found all the Informix Reference Books (sql syntax, sql tour ....), but
> got nothing!
CASE is a 7.3x extension. Look in the online manuals if you have that
version. If you have an earlier version it doesn't matter ;-( Always
useful to post your configurations: version, OS, Hdwr, etc.
Art S. Kagel
> thanks again!
>
> Jay Buckler <buckler@sover.net> wrote in message
> news:6wdE3.15040$N77.1119408@typ11.nn.bcandid.com...
> > This can be done with a single select using the case statement:
> >
> > select person, year,
> > sum(case when month = 1 then money else 0 end) M1,
> > sum(case when month = 2 then money else 0 end) M2,> > ...
> > sum(case when month = 12 then money else 0 end) M12
> > from sales_table
> > group by person, year
> >
> > Jay Buckler
> >
> > paul <paoching@panpi.com.tw> wrote in message
> > news:7rqgbk$mmh@netnews.hinet.net...
> > > 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!
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >