# Can we combine the rows with same key into one row?

Author Message
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Fri Dec 01, 2017 10:29 pm

Hi All,

I tried Google and couldn't find much help, I tried self-join with different permutations as well. I'm posting because I am stuck with this. Can anyone please give a hint if you have come across the same situation?

The sample data in the table.
 Code: Col1                         Col2         Col3 ABC                          2              1 ABC                          2              2 ABC                          2              3 CDE                          2              2 CDE                          2              3 FGH                          2              1 FGH                          2              3 IJK                          2              1 IJK                          2              2

Expected output
 Code: Col1                         Col2         Col3 ABC                          2              123 CDE                          2              23 FGH                          2              13 IJK                          2              12

The keys are Col1,Col2. The same key rows need to be merged and the col3 alone needs to be concatenated as shown above.
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

 Posted: Fri Dec 01, 2017 10:30 pm Mods - Sorry, accidentally posted in CLIST&REXX. Please move it to DB2 section.
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

Posted: Sat Dec 02, 2017 4:51 am

There may be better methods but one way to achieve this is by using multiple LEFT JOINs. The below is tested on sqlfiddle (that runs MySQL), so you may need to change the function for numeric conversion. So, in place of Convert, use DIGITS/CAST.. whatever you like.

I made a couple of assumptions like starting with a min value and max of 3 concatenations.

 Code: Select k.mod_col1, k.mod_col2, Max(Convert(k.mod_col3, signed integer)) from ( Select a.col1 as mod_col1, a.col2 as mod_col2,      concat(a.col3,coalesce(b.col3,''),coalesce(c.col3,'')) as mod_col3   from t1 a   left join   t1 b   on a.col1 = b.col1    and a.col2 = b.col2    and a.col3 = (select min(x.col3) from t1 x                   where x.col1 = a.col1                   and x.col2 = a.col2)      and b.col3 = (select min(y.col3) from t1 y                   where y.col1 = b.col1                   and y.col2 = b.col2                      and y.col3 <> a.col3)      left join   t1 c   on b.col1 = c.col1    and b.col2 = c.col2    and c.col3 = (select min(z.col3) from t1 z                   where z.col1 = c.col1                   and z.col2 = c.col2                      and z.col3 <> a.col3                 and z.col3 <> b.col3)  ) k  Group by k.mod_col1, k.mod_col2;
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

Posted: Mon Dec 04, 2017 11:09 pm

I just learned... , We don't need to worry about multiple left joins per multiple rows for the same group.
 Code: select col1 , col2 ,        rtrim(replace(replace(replace(char(xmlserialize(XMLAGG(XMLELEMENT(NAME "x",col3) ) as clob(10000))) , '', ''),'',''),'',''))  as col3        from mytable        group by  col1 , col2 ;
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

 Posted: Tue Dec 05, 2017 4:59 am Thank you very much Rahul, it worked very well. Rohit - Is it to generate XML? I have not tried your code yet. So just asking.
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

 Posted: Tue Dec 05, 2017 5:19 am If I were you , I will understand each function and try first.
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Wed Dec 06, 2017 2:32 am

And I just broke that into one function at a time and executed and understood it. It was very much educational. I am not sure how you had come up with such an elegant way. It worked just awesome. I just tweaked one tiny bit and the final piece is here.
 Code: select col1 , col2 ,        replace(translate(char(xmlserialize(XMLAGG(XMLELEMENT(NAME "x",col3)) as clob(10000))),'',''),' ') as col3        from mytable        group by  col1 , col2 ;

Thank you so very much, it was really helpful.
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Wed Dec 06, 2017 10:22 pm

Apparently DB2V12 made this way easier with the introduction of LISTAGG function.

So the code would be something like this. Unfortunately we do not have V12 installed in our shop yet.

 Code: select col1,col2,LISTAGG(col3,' ') from mytable group by col1,col2
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

 Posted: Wed Dec 06, 2017 10:53 pm XMLAGG was already present hence it wasn't there so far .. here is the link. www.ibm.com/developerworks/community/blogs/e429a8a2-b27f-48f3-aa73-ca13d5b69759/entry/The_most_important_function_in_Db2_12_for_z_OS_function_level_501_LISTAGG?lang=en
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Wed Dec 06, 2017 11:53 pm

Yeah I too found XMLAGG before finding LISTAGG, but LISTAGG intrigued me as it is direct solution without a need to convert the data first into XML. So, I again changed the query when I found the XMLAGG. Also, replaced XMLELEMENT with XMLCAST.

It came up something like this.

 Code: CHAR(XMLSERIALIZE(XMLAGG(XMLCAST(col3 AS XML) ORDER BY col3)AS CLOB(10)))

Here ORDER BY is optional, I just added since I need them in order.
RahulG31

Active User

Joined: 20 Dec 2014
Posts: 446
Location: USA

 Posted: Thu Dec 07, 2017 12:43 am I never knew these things existed. But then, I never had such a requirement to find them out either. :)
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

 Posted: Thu Dec 07, 2017 12:44 am Haha.. good oneππ
V S Amarendra Reddy

Active User

Joined: 13 Sep 2006
Posts: 216
Location: USA

Posted: Thu Dec 07, 2017 1:40 am

 RahulG31 wrote: I never knew these things existed. But then, I never had such a requirement to find them out either.
Auryn

New User

Joined: 11 Jan 2006
Posts: 67
Location: Lower Saxony (DE)

 Posted: Wed Feb 07, 2018 3:45 pm Hi there, just for information: Which Db2 version do you use? Db2 v12 or v11 or ..., z/OS or LUW? I'm asking because I tried to use ListAgg via v11 z/OS some time ago and were not pretty successful. Not sure if it was my fault or... Anyway. An other possibility - which works also with older Db2 versions and on z/OS - is to use Common Table Expressions. No idea about the performance when processing lots of data but it worked pretty well. Sorry, that at the moment I can not provide a sample. Actually I don't have access to any database. But maybe in a few days...
Rohit Umarjikar

Global Moderator

Joined: 21 Sep 2010
Posts: 2673
Location: NYC,USA

Posted: Wed Feb 07, 2018 10:18 pm

 Quote: I'm asking because I tried to use ListAgg via v11 z/OS some time ago and were not pretty successful. Not sure if it was my fault or... Anyway.
Either it will give you function not present or it should work, it can not be half baked. Also you overlooked the link posted above.
 View Bookmarks All times are GMT + 6 Hours

 Topic Forum Replies Similar Topics Write records with 3 or less rows in ... DFSORT/ICETOOL 3 Build two or more rows given some con... DFSORT/ICETOOL 9 Reading subsequent rows in a query. DB2 12 NOT ABLE TO FIND DB2 ROWS DB2 2 Need to read duplicate rows from tabl... DB2 3
Search our Forums: