|
View previous topic :: View next topic
|
| Author |
Message |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
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 |
|
 |
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
| Mods - Sorry, accidentally posted in CLIST&REXX. Please move it to DB2 section. |
|
| Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
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))) , '</x><x>', ''),'<x>',''),'</x>','')) as col3
from mytable
group by col1 , col2 ; |
|
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| If I were you , I will understand each function and try first. |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
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. |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
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 |
|
|
RahulG31
Active User
Joined: 20 Dec 2014
Posts: 446
Location: USA
|
|
|
|
| I never knew these things existed. But then, I never had such a requirement to find them out either. :) |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| Haha.. good one😊😊 |
|
| Back to top |
|
|
V S Amarendra Reddy
Active User
Joined: 13 Sep 2006
Posts: 218
Location: USA
|
|
|
|
| RahulG31 wrote: |
| I never knew these things existed. But then, I never had such a requirement to find them out either. |
|
|
| Back to top |
|
|
Auryn
New User
Joined: 11 Jan 2006
Posts: 84
Location: Lower Saxony (DE)
|
|
|
|
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... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3076
Location: NYC,USA
|
|
|
|
| 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. |
|
| Back to top |
|
|
|
|
