Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
concat string across rows in db2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Raghuraman_New

New User


Joined: 03 Oct 2008
Posts: 43
Location: Chennai,India.

PostPosted: Fri Oct 30, 2009 3:06 pm    Post subject: concat string across rows in db2
Reply with quote

I need to concat string across rows of a column in a similar fashion as how aggregate functions work .

For eg :-

TABLE1 :-
Code:
 
   WORD_NUM           WORD
      01               HOW
      01               TO
      01               CONCATENATE
      02               PLEASE
      02               HELP


Ans :-
I expect the follwing rows

01 HOW TO CONCATENATE
02 PLEASE HELP
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Oct 30, 2009 3:58 pm    Post subject:
Reply with quote

this is the fun way (found all over the internet):
Code:
select wordno,   replace(replace(cast(xml2clob(
         XMLAGG(xmlelement(name "A",strip(wordl)))
        ) as varchar(3000))
      ,'<A>','')
   ,'</A>',';')
from table
group by wordno

It is not very clear, with the same query you could get
01 CONCATENATE HOW TO
02 HELP PLEASE

if your table should be
Code:
 No_sent No_word              xWord
     1         1           HOW
     1         2           TO
     1         3           CONCATENATE
     2         1           PLEASE
     2         2           HELP

you could code some recursive SQL :
Code:
with sentences (no_sent,no_word,xsent) as (
select  no_sent, 1, word from table1
where obid = 1
union all
select  a.no_sent, a.no_word + 1, a.xsent !! ' ' !! b.word from sentences A , table1 B
where a.no_sent = b.no_sent and b.no_word = a.no_word+1
and a.no_word < 10  -- = Maximum to make sure you don't get into a loop
)

select * from sentences a
where no_word = (select max(no_word) from sentences b where a.no_sent = b.no_sent)
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts line 52: Name or string > 250 char... Ashishpanpaliya CLIST & REXX 3 Sat Oct 14, 2017 2:29 am
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Search for a string in many PDS KARTHIGADEVI CLIST & REXX 5 Fri Sep 08, 2017 2:32 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us