Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Can we combine the rows with same key into one row?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Fri Dec 01, 2017 10:29 pm    Post subject: Can we combine the rows with same key into one row?
Reply with quote

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.
Back to top
View user's profile Send private message

V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Fri Dec 01, 2017 10:30 pm    Post subject:
Reply with quote

Mods - Sorry, accidentally posted in CLIST&REXX. Please move it to DB2 section.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 411
Location: USA

PostPosted: Sat Dec 02, 2017 4:51 am    Post subject: Reply to: Can we combine the rows with same key into one row?
Reply with quote

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;
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Mon Dec 04, 2017 11:09 pm    Post subject:
Reply with quote

I just learned... icon_smile.gif, 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))) , '</x><x>', ''),'<x>',''),'</x>',''))  as col3   
    from mytable
       group by  col1 , col2 ;
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Tue Dec 05, 2017 4:59 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Tue Dec 05, 2017 5:19 am    Post subject: Reply to: Can we combine the rows with same key into one row?
Reply with quote

If I were you , I will understand each function and try first.
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Wed Dec 06, 2017 2:32 am    Post subject:
Reply with quote

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))),'','<x></x>'),' ') as col3   
    from mytable
       group by  col1 , col2 ;


Thank you so very much, it was really helpful. icon_smile.gif
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Wed Dec 06, 2017 10:22 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Wed Dec 06, 2017 10:53 pm    Post subject:
Reply with quote

XMLAGG was already present hence it wasn't there so far .. here is the link.
https://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
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Wed Dec 06, 2017 11:53 pm    Post subject: Reply to: Can we combine the rows with same key into one row?
Reply with quote

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.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 411
Location: USA

PostPosted: Thu Dec 07, 2017 12:43 am    Post subject: Reply to: Can we combine the rows with same key into one row?
Reply with quote

I never knew these things existed. But then, I never had such a requirement to find them out either. :)
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1827
Location: NY,USA

PostPosted: Thu Dec 07, 2017 12:44 am    Post subject:
Reply with quote

Haha.. good one😊😊
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 208
Location: USA

PostPosted: Thu Dec 07, 2017 1:40 am    Post subject: Re: Reply to: Can we combine the rows with same key into one row?
Reply with quote

RahulG31 wrote:
I never knew these things existed. But then, I never had such a requirement to find them out either. icon_smile.gif
icon_smile.gif icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us