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.

 
Go Back  dBforums > Database Server Software > DB2 > Concatenating strings from multiple rows in SQL

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 02-26-07, 19:14
jinsezh jinsezh is offline
Registered User
 
Join Date: Aug 2006
Posts: 33
Question Concatenating strings from multiple rows in SQL

Hi there,

I wonder if it is possible to concatenating a big string from multiple rows?

The table contains multiple rows for each claim_number :

claim_number claim_describe
1 des1.1
1 des1.2
...

And I want to get
Claim_number Claim_describe
1 des1.1; desc1.2; ...

I can only use SQL.

Thanks a lot
Reply With Quote
  #2 (permalink)  
Old 02-27-07, 04:13
grofaty grofaty is offline
Registered User
 
Join Date: Jan 2003
Posts: 1,570
Hi,
if you know the number of repeating row then you can write.

For two rows:
Code:
select a.claim_describe concat '; ' concat b.claim_describe
from table as a, table as b
where a.claim_number = b.claim_number
For three rows:
Code:
select a.claim_describe concat '; ' concat b.claim_describe concat '; ' concat c.claim_describe
from table as a, table as b, table as c
where a.claim_number = b.claim_number and a.claim_number=c.claim_number
Hope this helps,
Grofaty
Reply With Quote
  #3 (permalink)  
Old 02-27-07, 04:33
umayer umayer is offline
Registered User
 
Join Date: Dec 2005
Posts: 273
Quote:
Originally Posted by grofaty
For two rows:
Code:
select a.claim_describe concat '; ' concat b.claim_describe
from table as a, table as b
where a.claim_number = b.claim_number

In this case, each claim_number will appear twice.
( with description "desc1.1 ; desc1.2" and "desc1.2 ; desc1.1" )
to avoid this you might add
...AND a.claim_describe < b.claim_describe
to the WHERE-clause.

But if you know there are always exactly two entries per claim_number, I'd suggest:

Code:
SELECT claim_number , MIN(claim_describe) CONCAT ';' CONCAT MAX(claim_describe) 
FROM table
GROUP BY claim_number
Reply With Quote
  #4 (permalink)  
Old 02-27-07, 07:52
stolze stolze is offline
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
Recursive SQL seems to be the way to go. Search for "Bill of materials" in the manual.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #5 (permalink)  
Old 02-27-07, 09:29
sathyaram_s sathyaram_s is offline
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,534
This is what Knut recommends (above)

This will work for any number of rows of claims.

Code:
--- your original claims table - created on-the-fly for testing
--- added claim number 2 and 3  for example purpose 
with claims (claim_number , claim_describe)  
as
(
values 
(1,  'des1.1'),
(1,  'des1.2'),
(2,'des2.1'),
(2,'des2.2'),
(2,'des2.3'),
(3,'desc3.1')
)
,
-- allocate row numbers - to obtain the sequence of concatenation 
claims_rown(rownum,claim_number,claim_describe) as 
(
select row_number() over (partition by claim_number order by claim_describe),claim_number,claim_describe from claims
)
,
-- Using recursion to concatenate claim_describe column
claims_concat(cur_rownum,cc_claim_number,concat_claim_describe) as 
(
-- 'Initialize' recursion table by fetching only the first row for each calim_number. 
select  rownum,claim_number,varchar(claim_describe,1000) from claims_rown where rownum=1 
union all   
-- Concatenate the claim_describe of current row with the previously concatenated claim_describe string 
select rownum,claim_number,concat_claim_describe||','||claim_describe from claims_rown,claims_concat where rownum=cur_rownum+1 and claim_number=cc_claim_number
)
--- Now get only the last row of the resultset for each claim_number 
select cc_claim_number as claim_number,concat_claim_describe  as claim_describe from claims_concat where (cc_claim_number,cur_rownum) in (select claim_number,max(rownum) from claims_rown group by claim_number)
HTH

Sathyaram
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #6 (permalink)  
Old 02-28-07, 14:46
Peter.Vanroose Peter.Vanroose is offline
Registered User
 
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
This question was essentially anwered in the following thread: http://www.dbforums.com/ansi-sql/1612892-query-special-transpose-merge-data.html#post6248263
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On