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-l