How do I concatenate strings from a column into a single row in DB2

How do I concatenate strings from a column into a single row?
>
Whats the logic to create the function in DB2.
>
Given is below.
>
Color
------
red
orange
blue
green
>
And return a resultset like this:
>
Colors
-------------------------
red,orange,blue,green

On Nov 19, 2:28*pm, mark83anth...@gmail.com wrote:
>
>
>>>>>>>
I will make it clear.
>
The table name is Colors
CREATE TABLE Colors
(
* * Color VARCHAR(32)
)
>
Values in the Table are
Color
------
red
orange
blue
green
>
My output should be when I do a Select * from Tabname, shd be
>
red,orange,blue,green
>
Waiting for your reply
>
Thanks in Advancd

On Nov 19, 12:49*pm, mark83anth...@gmail.com wrote:
>
>
>
>
>>>>>>>>>>>>>>>
Mark:
>
Have a look at a posting to this newsgroup titled "concatenating
historical records" from October '07. In it, I listed two ways I know
of/have learned to do this.
>
--Jeff- Hide quoted text -
>
- Show quoted text -

On Nov 19, 3:53*pm, jefftyzzer <jefftyz...@sbcglobal.netwrote:
>
>
>>>>>>>>>>>>>>>>>>>>
Hi,
>
I tried doing this
>
SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
Color))),'<A>',''),'</A>',' ')
FROM Colors;
>
It works fine with my DB2 9.5 client on my machine but does not work
with Db2 Z OS 9.1.
>
It gives me the following error message
>
SQL0171N *The data type, length or value of argument "1" of routine
"REPLACE"
is incorrect. *SQLSTATE=42815
>
SQL0171N *The data type, length or value of argument "1" of routine
"REPLACE" is incorrect.
>
Explanation:
>
The data type, length or value of argument "<n>" of routine "<name>"
is
incorrect.
>
The statement cannot be processed.
>
User response:
>
Ensure the arguments of the routine conform to the rules of the
routine.
>
*sqlcode: -171
>
*sqlstate: 42815

SELECT replace(replace(xmlagg(xmlelement(NAME a,NAME)),'<A>',' ' ),'</A>',' ') FROM TECPG01.TEST1111
>
It works fine with db2 on LUW but generates an error for Db2 on z/OS
saying that argument 1 of the routine REPLACE is wrong.
>