| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-16-10, 07:48
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 52
|
|
|
Help with concatanate..
|
|
Hi Guys,
I have a table with two concerned columns. One column is id and other is comment. One ID can have multiple comments. I want to concatanate all the comments for a given id in SQL.
For an Example: if the i/p is
Code:
ID Comment
1 hai
1 me
1 is
1 gud
2 heim
3 out
3 of
I want to have the output as follows :
Code:
ID Comment
1 hai,me,is,gud
2 heim
3 out,of
Thanks For your help in advance.
Regards,
Magesh
|
|
04-16-10, 08:13
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
This question gets asked a lot. You need a recursive query to do that. Search this forum for pivot table and you should get examples on how to do this.
Andy
|
|
04-16-10, 08:30
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
AFAIK, XMLGROUP would be an easiest way on DB2 for LUW 9.5 or later.
Search this forum with "XMLGROUP concatenate strings".
|
|
04-16-10, 11:33
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
LEFT JOIN TABLE, only
The Problem could be solved with LEFT JOIN TABLE, only.
In this case we have to know the maximum number occurrences for ID in YOUR_TABLE, for example 5:
Code:
with Your_table(ID, Comment) as
(
select 1, 'hai' from sysibm.sysdummy1 union all
select 1, 'me' from sysibm.sysdummy1 union all
select 1, 'is' from sysibm.sysdummy1 union all
select 1, 'gud' from sysibm.sysdummy1 union all
select 2, 'heim' from sysibm.sysdummy1 union all
select 3, 'out' from sysibm.sysdummy1 union all
select 3, 'of' from sysibm.sysdummy1
)
select t1.id,
t1.Comment1
|| ifnull(', ' || t2.Comment2, '')
|| ifnull(', ' || t3.Comment3, '')
|| ifnull(', ' || t4.Comment4, '')
|| ifnull(', ' || t5.Comment5, '')
as Comments
From
(select id, min(Comment) Comment1
from Your_table
group by id ) t1
left join table
(select i2.id, min(i2.Comment) Comment2
from Your_table i2
where t1.id = i2.id
and i2.Comment > t1.Comment1
group by id ) t2
on t1.id = t2.id
left join table
(select i3.id, min(i3.Comment) Comment3
from Your_table i3
where t1.id = i3.id
and i3.Comment > t2.Comment2
group by id ) t3
on t1.id = t3.id
left join table
(select i4.id, min(i4.Comment) Comment4
from Your_table i4
where t1.id = i4.id
and i4.Comment > t3.Comment3
group by id ) t4
on t1.id = t4.id
left join table
(select i5.id, min(i5.Comment) Comment5
from Your_table i5
where t1.id = i5.id
and i5.Comment > t4.Comment4
group by id ) t5
on t1.id = t5.id
Result:
Quote:
ID.......... COMMENTS
1........... gud, hai, is, me
2........... heim
3........... of, out
|
Lenny
|
|
04-16-10, 11:59
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Even much easier
We can also simplify the query:
Code:
with Your_table(ID, Comment) as
(
select 1, 'hai' from sysibm.sysdummy1 union all
select 1, 'me' from sysibm.sysdummy1 union all
select 1, 'is' from sysibm.sysdummy1 union all
select 1, 'gud' from sysibm.sysdummy1 union all
select 2, 'heim' from sysibm.sysdummy1 union all
select 3, 'out' from sysibm.sysdummy1 union all
select 3, 'of' from sysibm.sysdummy1
)
select t1.id,
t1.Comment
|| ifnull(', ' || t2.Comment, '')
|| ifnull(', ' || t3.Comment, '')
|| ifnull(', ' || t4.Comment, '')
|| ifnull(', ' || t5.Comment, '')
as Comments
From
(select id, min(Comment) Comment
from Your_table
group by id ) t1
left join table
(select t1.id, min(i2.Comment) Comment
from Your_table i2
where t1.id = i2.id
and i2.Comment > t1.Comment) t2
on 1 = 1
left join table
(select t1.id, min(i3.Comment) Comment
from Your_table i3
where t1.id = i3.id
and i3.Comment > t2.Comment) t3
on 1 = 1
left join table
(select t1.id, min(i4.Comment) Comment
from Your_table i4
where t1.id = i4.id
and i4.Comment > t3.Comment) t4
on 1 = 1
left join table
(select t1.id, min(i5.Comment) Comment
from Your_table i5
where t1.id = i5.id
and i5.Comment > t4.Comment) t5
on 1 = 1
Result:
Quote:
ID.......... COMMENTS
1........... gud, hai, is, me
2........... heim
3........... of, out
|
Lenny
|
|
04-16-10, 13:20
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
but Lenny this solution is only good if you have 4 or less comments. The other options posted would be best in a case like this, unless you have a rule that there will only ever be a max of N.
Dave
|
|
04-16-10, 14:38
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Any number of JOIN
Quote:
Originally Posted by dav1mo
but Lenny this solution is only good if you have 4 or less comments. The other options posted would be best in a case like this, unless you have a rule that there will only ever be a max of N.
Dave
|
You can add any number of LEFT JOINs together with concat in SELECT part.
Next example with 7 JOINs with same result:
Code:
with Your_table(ID, Comment) as
(
select 1, 'hai' from sysibm.sysdummy1 union all
select 1, 'me' from sysibm.sysdummy1 union all
select 1, 'is' from sysibm.sysdummy1 union all
select 1, 'gud' from sysibm.sysdummy1 union all
select 2, 'heim' from sysibm.sysdummy1 union all
select 3, 'out' from sysibm.sysdummy1 union all
select 3, 'of' from sysibm.sysdummy1
)
select t1.id,
t1.Comment
|| ifnull(', ' || t2.Comment, '')
|| ifnull(', ' || t3.Comment, '')
|| ifnull(', ' || t4.Comment, '')
|| ifnull(', ' || t5.Comment, '')
|| ifnull(', ' || t6.Comment, '')
|| ifnull(', ' || t7.Comment, '')
as Comments
From
(select id, min(Comment) Comment
from Your_table
group by id ) t1
left join table
(select t1.id, min(i2.Comment) Comment
from Your_table i2
where t1.id = i2.id
and i2.Comment > t1.Comment) t2
on 1 = 1
left join table
(select t1.id, min(i3.Comment) Comment
from Your_table i3
where t1.id = i3.id
and i3.Comment > t2.Comment) t3
on 1 = 1
left join table
(select t1.id, min(i4.Comment) Comment
from Your_table i4
where t1.id = i4.id
and i4.Comment > t3.Comment) t4
on 1 = 1
left join table
(select t1.id, min(i5.Comment) Comment
from Your_table i5
where t1.id = i5.id
and i5.Comment > t4.Comment) t5
on 1 = 1
left join table
(select t1.id, min(i6.Comment) Comment
from Your_table i6
where t1.id = i6.id
and i6.Comment > t5.Comment) t6
on 1 = 1
left join table
(select t1.id, min(i7.Comment) Comment
from Your_table i7
where t1.id = i7.id
and i7.Comment > t6.Comment) t7
on 1 = 1
Quote:
ID.......... COMMENTS
1........... gud, hai, is, me
2........... heim
3........... of, out
|
Lenny
|
|
04-16-10, 16:08
|
|
Registered User
|
|
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
|
|
but that is the problem I spoke of. You have to know how many outer joins to put into the statement and that is why it is not the best option. a single id could have N comments. How do you know how many outer joins to put in? THat is why recursion is the best option. Otherwise you would have to write your SQL with ever increasing number of outer joins and then how would you know when you had exceeded the outer joins that you had written.
Dave
|
|
04-16-10, 16:24
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by dav1mo
but that is the problem I spoke of. You have to know how many outer joins to put into the statement and that is why it is not the best option. a single id could have N comments. How do you know how many outer joins to put in? THat is why recursion is the best option. Otherwise you would have to write your SQL with ever increasing number of outer joins and then how would you know when you had exceeded the outer joins that you had written.
Dave
|
I shown recursion for this many times. Now I shown another way. Why not.
In dynamic SQL you can add N + 1 JOIN without any problem.
You don't need to rename inside tables with I1 -- In. You can use I1 for all of them and that it.
Lenny
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with concatanate..
