I have following sample data
COL1 COL2
----------- ----------
1 Welcome
1 udb
1 database
2 Toronto
2 canada

I want to run a aggreagte function on col2 in such a manner , my select output should be

1 welcome udb database
2 toronto canda

Thx For help

I discussed with my co-work before for similar question, and the conclusion is that there is no way to do this in a single standard sql query(no external UDF or stored proc). And should be fair easy and efficient by using an application. (for example StringBuffer class in java).

Anyway, with the latest XML functions, it is doable with XMLAGG and XMLQUERY functions, such as(run on db2/zos):


CREATE TABLE TT
(COL1 VARCHAR(10));

INSERT INTO TT VALUES('welcome');
INSERT INTO TT VALUES('udb');
INSERT INTO TT VALUES('db2base');

SELECT
XMLSERIALIZE(
XMLQUERY('/fn:data(COL1)' PASSING
XMLDOCUMENT(
XMLAGG(XMLELEMENT(NAME "COL1", COL1))) )
AS CLOB(50) EXCLUDING XMLDECLARATION)
FROM TT;


+----------------------------------------------------+
| |
+----------------------------------------------------+
1_| welcome udb db2base |
+----------------------------------------------------+


DB2/LUW should support similar functions, but you need V9.

Anyway, I won't suggest you use above method(which suffer too much overhead when deal with xml).
Hope someone else has smarter ideas.

This can be achieved with recursive SQL using CTEs; see e.g. the threads http://www.dbforums.com/ansi-sql/1612892-query-special-transpose-merge-data.html#post6248263, Concatenate column values from multiple rows and How to concat several lines into one
Note that you need to specify the concatenation order in some way or the other, e.g. through a third column. (Aggregate SUM, COUNT etc. are order-indifferent, CONCAT isn't.)

Wrong conclusion.

Besides the recursive queries that Peter referred to, you can use DB2's XML capabilities and the XMLAGG function. Here is an example:
http://www.mydatabasesupport.com/for...cte-udf.html#6

I got confused from your comments. Didn't I provide an xmlagg example in the original comments?

I don't think standard sql including xml tech. XML is more like another tech(other than sql), but happened to be implemented by IBM inside db2.