Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help on Query
At 10:30 AM Mandal, Ashoke wrote something which looks depressingly like
a homework assignment:
> Greetings,
>
> Could somebody help on the following.
_help_, yes.
> Thanks,
> Ashoke
>
> I have a table with the following values
>
> Lot# lot_desc start_date qty
> 1 Sample 09/02/2005 1
> 1 Sample 09/03/2005 2
> 2 Sample 09/02/2005 1
> 2 Sample 09/03/2005 2
> 3 Sample 09/02/2005 1
> 3 Sample 09/03/2005 2
> 4 Sample 09/02/2005 1
> 4 Sample 09/03/2005 2
> 4 Sample 09/04/2005 1
> 4 Sample 09/05/2005 2
>
> The output must be like the following:
>
> Lot#1 Lot_desc sum(qty) 09/02/2005 09/03/2005 09/04/2005 09/05/2005
> 1 Sample 3 1 2
> 2 Sample 3 1 2
> 3 Sample 3 1 2
> 4 Sample 6 1 2 1
> 2
For help with the more general case, my advice is to do a search on asktom.oracle.com using "sql query pivot".
For this specific case, remember that every column you want in your result must be included as a column in your select clause. Thus, you are going to need a select clause like:
select lot_no, descrip, sum(qty), sum(col1), sum(col2), sum(col3), sum(col4)
The trick is that you use CASE or DECODE statements to where to put each row of your table into a subquery. You want to transform your example table into something which looks like this:
lot_no descrip qty col1 col2 col3 col4
1 Sample 1 1 0 0 0 1 Sample 2 0 2 0 0 2 Sample 1 1 0 0 0 2 Sample 2 0 2 0 0 3 Sample 1 1 0 0 0 3 Sample 2 0 2 0 0 4 Sample 1 1 0 0 0 4 Sample 2 0 2 0 0 4 Sample 1 0 0 1 0 4 Sample 2 0 0 0 1
where each coli is obtained from a CASE or DECODE expression.
Once you have written something which gives you the above, you ought to find it easy to use it to get your result.
> Is this possible with one single statement? If so, could you please send
Yes
> me the query.
If this didn't look so much like a homework assignment, I probably would have.
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 08 2005 - 18:55:53 CDT