GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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) |
|
|
