Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: 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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

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

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This question was essentially anwered in the following thread: http://www.dbforums.com/showthread.php?p=6248263
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •