Portal | IBM Manuals | Downloads | Products | Refer | Info | Programs | JCLs | Forum Rules | Buy Our Software DVD 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index
 
Register
 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index FAQ Search 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
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: 1242
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
Display posts from previous:   
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 



 
Master the Mainframes | Bookmarks | Subscriptions | Site Map | Polls | Photos | FAQ | Rules | Contact Us