Merging Multiple Rows

Analytics

Merging Multiple Rows

Hey All - can someone please help me?

i.e I have the following

select

Decription

Sku

Chain ID

And my results are

Description         SKU        CHN-ID

Cell Phones        1001       999

Cell Phones        1001       111

How would I get it to return:

Description         SKU        CHN_ID

Cell Phones            1001       999,111

Thanks!

2 REPLIES
Supporter

Re: Merging Multiple Rows

check recursive queries - there are plenty of examples in this forum

Enthusiast

Re: Merging Multiple Rows

Not sure you got the answer to your query or not, but a quick solution would be to change the definition of the table and include a new column lets say "Seq", which could have the same content as the number CHN_ID - This new column will be used to restrict the results of the recursive query.

Following query should give you the requred results!

WITH RECURSIVE rec_table (Description, SKU, CHN_ID, Seq) AS
(
SELECT tblA.Description, tblA.SKU, cast(tblA.CHN_ID as varchar(10)), tblA.Seq
FROM test_table tblA
inner join
(
SEL Description, SKU, min(SEQ) as SEQ
from test_table
group by Description, SKU
) tblB
on tblA.Description = tblB.Description
and tblA.SKU = tblB.SKU
and tblA.SEQ = tblB.SEQ

UNION ALL

select tblC.Description, tblC.SKU, cast(tblC.CHN_ID as varchar(10)) || '-' || cast(tblD.CHN_ID as varchar(10)), tblC.Seq
from rec_table tblD, test_table tblC
where tblC.Description = tblD.Description
And tblC.SKU = tblD.SKU
And tblC.Seq > tblD.Seq

)

SELECT r.*
FROM rec_table r
inner join
(
select description, SKU, max(seq) as seq
from test_table
group by description, sku
) tblE
on r.Description = tblE.Description
and r.SKU = tblE.SKU
and r.SEQ = tblE.SEQ

Regards,