-
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?
-
Re: A Query Doubt
Select a <br> , max(case column# when 1 then b end) -- first column <br> -- if 2nd column not null, append after comma <br> || case when <br> max(case column# when 2 then b end) <br> is not null then ','|| <br> max(case column# when 2 then b end) <br> end <br> -- if 3rd column not null, append after comma <br> || case when <br> max(case column# when 3 then b end) <br> is not null then ','|| max(case column# when 3 then b end) <br> end as resulting_csv <br> from ( select a.ryotno ||' '||a.suppqty ||' '||a.gross a,b.amount b, row_number() over <br> (partition by a.ryotno <br> order by a.ryotno ) column# <br> from cmscaneweighment a,cmsloanissues b where a.ryotno=b.ryotno ) <br> group by a <br> order by a
Try this