Concatenating rows ?

James Conrad StJohn Foreman

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

Brian Tkatch

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

B.

Mar 10 '06 #2

James Conrad StJohn Foreman

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

Serge Rielau

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

Brian Tkatch

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