Query Support for DB2

[Former Member]

my query is as below

select sa.JOB_ID, sa.BRANCH_ID, coalesce(sa.REDUNDANT_STOCK_ID, '') as REDUNDANT_STOCK_ID

        from STOCK_ARRIVAL SA

        where   sa.job_id >= '1473187' 

            and sa.JOB_ID <= '1473187'

            and sa.BRANCH_ID = 'S'

        group by sa.JOB_ID, sa.BRANCH_ID, coalesce(sa.REDUNDANT_STOCK_ID, '')

and i am getting a output as

JOB_ID	BRANCH_ID	REDUNDANT_STOCK_ID
1473187	S
1473187	S	811516
1473187	S	811517

but i need the output as

JOB_ID	BRANCH_ID	REDUNDANT_STOCK_ID
1473187	S	811516, 811517

any one can help me on the query

[Former Member · Accepted solution]

Try this

with temp (JOB_ID_A, etyp, rn) as (select JOB_ID, cast(REDUNDANT_STOCK_ID as     

varchar(1000)), row_number() over (partition by JOB_ID order by    

REDUNDANT_STOCK_ID) from STOCK_ARRIVAL SA),

rec (JOB_ID_A, list, rn) as (select t.JOB_ID_A,

t.etyp, t.rn from temp t where t.rn = 1 union all select t.JOB_ID_A,   

r.list concat ', ' concat t.etyp, t.rn from temp t inner join rec r

on r.JOB_ID_A = t.JOB_ID_A and t.rn = r.rn+1), pres (JOB_ID_A, data, rn) as    

(select JOB_ID_A, list, row_number() over (partition by JOB_ID_A order by  

rn desc) from rec)

select JOB_ID_A as "JOB ID", data as "STOCK ID" from pres where rn = 1

ORDER    BY CAST(JOB_ID_A AS DEC(10))