how to concatenate rows in db2 for one column?
it is required that no temporary tabel variables be used

Class assignment ?

nope absolutely not i changed the names of the columns to have it understood with an ease

You need aggregation. Have a look at the XMLAGG function.

the xmlagg function returns result in xml format which is not wanted i tried the aggregate function, i have researched on concat in db2, group_concat in mysql but it doesnt help i implemented this using plsql where i used table variables but here i am restricted from using them as my sysadmin doesnot allow me to do it

What's the problem with XML data types?

Have a look here at the first answer: http://www.thescripts.com/forum/thread542844.html
Pretty much the same is done there: regular strings are converted to XML, then XMLAGG combines this to a single scalar (XML) value, then you serialize it to a string and finally strip-out the XML tags. Homework done!

substr(replace(replace(
xmlserialize(content xmlagg(
xmlelement(name \"c\", colname) order by colno)
would do but i have around 24000 such rows to be checked for concatenation of the lists in the table so would iterating around it be fruitful?

the xmlagg function returns result in xml format which is not wanted as i will have to save this information in an already existing table and i cannot use a temp table to save these values i tried the aggregate function, i have researched on concat in db2, group_concat in mysql but it doesnt help i implemented this using plsql where i used table variables but here i am restricted from using them as my sysadmin doesnot allow me to do it and also aggregate functions imply to columns in the table

The number of rows doesn't matter - this is SQL, which is tailored to processing multiple rows. The argument with "xml format" is bogus because you can convert between strings and XML without any problems. So where exactly do you get stuck?

If there really is a (relatively low) upper bound on the number of values in the last column (4 here, it seems from the doc), this can be achieved with a self-join (4x the same table, left outer join) and a "<" join condition.
(Will be far more expensive than the XMLAGGR solution, though.)

Given the first statement I quoted here, I don't understand at all why you initially said that no "variables, temp tables, global temp tables, or common table expressions (with clause)" are an option. Usually, such restrictions are imposed only in class exercises. Practical problems often allow you to search for all possible solutions and then picking the best approach - instead of reducing the solution space right away as you have done. I guess you will understand that all who responded to your question have this impression.

Could you explain to us why recursive queries (common table expressions) are not allowed, for instance? You could implement the CTE inside a table function and then use this table function in an INSERT statement - no materialized temp tables will be required (but DB2 uses a barrier to first build an intermediate result in memory, which is essentially a temp table) and the recursive CTE can be used in a straight-forward manner to do the concatenation. Likewise, XMLAGG does the aggregation of a bunch of XML values. The links provided above show how to use that function for your purposes. You can even throw this into a subselect and do whatever you want. You can process multiple nested groups on millions of rows if you like. Therefore, I think it is fair to raise the question what the problems with XMLAGG are. What have you tried? How were it failing/not producing the correct results?

I didn't understand this part. Could you please explain what you mean by it? If you mean by "columns in the table" a column in a base table, you are mistaken. You can do aggregation on anything in SQL because - at the end of the day - everything is a table in SQL. Even a simple statement like this produces a table:
This is a table with one row and one column, so you could aggregate on it (or do whatever other features SQL provides, for example:
The only important part is that you properly define your groups on which an aggregation is to be applied.

p.s: I don't intend to bash MySQL now, but the way how some DBMS adhere to the SQL standard and implement relational concepts is sometimes not the best. Therefore, drawing conclusions for one DBMS based on experiences gathered on MySQL is often not a good idea.

all these are specifications for the problem

Then have fun with your exercise.

I am basically a java developer and i am asked to write this sp for a db2 database and my dba restricted me in using temptables for this sp anywaz thanks for your suggestions

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.