-
Expert Member
A Query Doubt
I am using the following two tables
cmscaneweighment
ryotno suppqty gross
02098u002 54.665 49199
02107d005 17.142 15428
cmsloanissues
ryotno loantype amount
02098u002 manure loans 2500
02098u002 advance 1500
02107d005 manure loans 5000
02107d005 seed loan 7500
02107d005 advance 7000
The output should be as follows
ryotno suppqty gross manure avance seedloan
02098u002 54.665 49199 2500 1500 0
02107d005 17.142 15428 5000 7000 7500
can we get the above output in a sigle query without writing a procedure?
Last edited by krishnaindia2007; 12-31-2007 at 06:33 AM.
-
Expert Member
Re: A Query Doubt
Select a
, max(case column# when 1 then b end) -- first column
-- if 2nd column not null, append after comma
|| case when
max(case column# when 2 then b end)
is not null then ','||
max(case column# when 2 then b end)
end
-- if 3rd column not null, append after comma
|| case when
max(case column# when 3 then b end)
is not null then ','|| max(case column# when 3 then b end)
end as resulting_csv
from ( select a.ryotno ||' '||a.suppqty ||' '||a.gross a,b.amount b, row_number() over
(partition by a.ryotno
order by a.ryotno ) column#
from cmscaneweighment a,cmsloanissues b where a.ryotno=b.ryotno )
group by a
order by a
Try this
Last edited by susarlasireesha; 12-31-2007 at 07:45 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules