Concatenating rows ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Conrad StJohn Foreman - 10 Mar 2006 17:40 GMT

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

Reply to this Message

Brian Tkatch - 10 Mar 2006 18:01 GMT

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

B.

Reply to this Message

James Conrad StJohn Foreman - 13 Mar 2006 19:54 GMT

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?

Reply to this Message

Serge Rielau - 13 Mar 2006 22:23 GMT

> Thanks Brian, Serge.
>
[quoted text clipped - 8 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Brian Tkatch - 15 Mar 2006 16:08 GMT

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.

Reply to this Message