| |
|
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.
|
 |

02-26-07, 19:14
|
|
Registered User
|
|
Join Date: Aug 2006
Posts: 33
|
|
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
|
|

02-27-07, 04:13
|
|
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
|
|

02-27-07, 04:33
|
|
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
|
|

02-27-07, 07:52
|
|
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
|
|

02-27-07, 09:29
|
|
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.
|
|

02-28-07, 14:46
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
|
__________________
--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting
__http://www.abis.be/
|
|
| 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
|
|
|
|
|