Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1272
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 String in Cobol janmejay COBOL Programming 7 Sun Jun 26, 2016 11:28 am
No new posts How do I right justify a string in an... rakesh17684 DB2 1 Wed Jun 08, 2016 8:01 pm
No new posts Column into multiple rows V S Amarendra Reddy SYNCSORT 2 Thu Mar 03, 2016 8:22 pm
No new posts Get rows based on amount entered from... Rohit Umarjikar DB2 10 Thu Feb 25, 2016 9:57 pm
No new posts Finding a File Containing a Particula... rexx77 DFSORT/ICETOOL 8 Tue Dec 22, 2015 2:19 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us