Thank you very much. Can you please explain me what is "Pivot Query"?
Dmytro Dekhtyaryuk wrote:
> "Scott" <sivaraman55_at_gmail.com> schrieb im Newsbeitrag
> news:1130125732.777489.98430_at_o13g2000cwo.googlegroups.com...
> >I have two tables. Table1 and Table2.
> >
> > Table 1 has coloumns
> > colID
> > colSt -- The values are A, B, C and D.
> > colDate
> > ColMachinery
> >
> > Table 2 has columns
> > colID
> > colType - W, X, Y or Z
> > colInventory
> >
> > The input to the query is Date Range(colDate of Table 1). ColID is the
> > primary and foreign key for the tables.
> > Now I want a query that will list the output something like this..
> >
> > Date Total of colSt Total of colSt Total of TOtal of Total of
> > Total of
> > with Status A with Status B type W type X type Y
> > type Z
> >
> >
> > Can someone help?
> >
> > Thanks in advance,
> > Scotty.
> >
>
> It's a pivot query:
> select coldate,
>
> sum(decode(colst, 'A', colMachinery + colInventory)) "Total with Status A",
>
> sum(decode(colst, 'B', colMachinery + colInventory)) "Total with Status B",
>
> sum(decode(colst, 'C', colMachinery + colInventory)) "Total with Status C",
>
> sum(decode(colst, 'D', colMachinery + colInventory)) "Total with Status D",
>
> sum(decode(coltype, 'W', colMachinery + colInventory)) "Total of Type W",
>
> sum(decode(coltype, 'X', colMachinery + colInventory)) "Total of Type X",
>
> sum(decode(coltype, 'Y', colMachinery + colInventory)) "Total of Type Y",
>
> sum(decode(coltype, 'Z', colMachinery + colInventory)) "Total of Type Z"
>
> from scott.table1 t1, scott.table2 t2
>
> where t1.colid = t2.colid
>
> group by coldate
>
> Regards
>
> Dmytro Dekhtyaryuk
Received on Mon Oct 24 2005 - 05:43:55 CDT
