All Forums Analytics
seifhabib83 1 post Joined 07/12
10 Jul 2012
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!

ulrich 816 posts Joined 09/09
11 Jul 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

Qaisar Kiani 337 posts Joined 11/05
14 Jul 2012

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,

You must sign in to leave a comment.