By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,007 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,007 IT Pros & Developers. It's quick & easy.

Concatenating rows ?

P: n/a
I've got a table full of search terms, one search term per row.

ID CATEGORYID SEARCH_TERM
1 1 cat
2 1 mat
3 2 sat
....

I need to get the search terms concatenated - is there an easy way to
achieve this?

If I knew how many search terms there were, I could join the table to
itself:

SELECT a.search_term || b.search_term
FROM search_terms a, search_terms b
WHERE a.categoryid = b.categoryid
AND a.id < b.id

(and expand this for n tables).

But I don't know how many search terms there are. Is there a simple
way to do this? Or is it some kind of repeating function that I need
to program?

Thanks

JCSJF

Mar 10 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Serge posted this one not too long ago. Do a search in this group for
XMLAGG.

B.

Mar 10 '06 #2

P: n/a
Thanks Brian, Serge.

select xmlagg(xmlelement(NAME a, email_address)) FROM
advertising.recipients;

Gives me
Function not supported (Reason code = "58"). SQLSTATE=42997

:-(

Running DB2 Express 8.2.1 on SLES 8. Do I need to be installing XML
extenders or something?

Mar 13 '06 #3

P: n/a
James Conrad StJohn Foreman wrote:
Thanks Brian, Serge.

select xmlagg(xmlelement(NAME a, email_address)) FROM
advertising.recipients;

Gives me
Function not supported (Reason code = "58"). SQLSTATE=42997

:-(

Running DB2 Express 8.2.1 on SLES 8. Do I need to be installing XML
extenders or something?

Wrap it into XML2CLOB(...)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 13 '06 #4

P: n/a
XML is an internal data type that is the required input for some
FUNCTIONs, and others output. However, it is not supported while
interfacing with the user.

The main FUNCTION is XMLAGG(), however, it requires the XML data typeas
its input, and outputs it as its datatype too. To make text into XML,
XMLElement() both changes the text to be XML and changes the data type
to XML. So much for the input. For the output, (as Serge pointed out)
XML2CLOB() turns it into a viewable data type.

Then you need to strip the XML tags (try select
substr(xml2clob(xmlelement(NAME a, email_address)), 1, 50) FROM
advertising.recipients; to see it). Which is <A> and </A> ("A" is the
NAME you specified.) So, REPLACE does that.

Finally, if you don't actually want a clob, SUBSTR() takes care of
that.

B.

Mar 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.