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