Thread: 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?

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