Combine column multiple rows into one row

[Antanas]

Is it possible to combine multiple rows returned from select statement
into one row?

SELECT NAME FROM TABLE1;

I want all names to be combined into one row seperated by commas.

[Knut Stolze]

Antanas wrote:

Is it possible to combine multiple rows returned from select statement
into one row?

SELECT NAME FROM TABLE1;

I want all names to be combined into one row seperated by commas.

Use recursive SQL to do the concatenation. Examples are posted here every
few weeks...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Antanas]

Thanks Knut.

[Serge Rielau]

Antanas wrote:

Is it possible to combine multiple rows returned from select statement
into one row?

SELECT NAME FROM TABLE1;

I want all names to be combined into one row seperated by commas.

This is quickly turning into a FAQ. What's going on all of a sudden?Wat
is this used for?
Ideally pretty printing is the job of your application.
Anyway this is one of many way to do it (untested):

WITH source(rn, name) AS (SELECT ROW_NUMBER() OVER() AS rc,
name
FROM T),
rec(n, name) AS (SELECT 2, CAST(name AS VARCHAR(2000)
FROM source WHERE rn = 1
UNION ALL
SELECT n+1, rec.name || ',' || source.name
FROM source WHERE n = rn)
SELECT name FROM rec ORDER BY n DESC FETCH FIRST ROW ONLY;

If you have a primary key on the table you don't need the ROWNUMBER() mess.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.

So be sure to comment the code so people will know that you are
destroying 1NF on purpose and do not know about C/S architecture. That
might help someone maintian the code and correct it after you are gone.

[Buck Nuggets]

--CELKO-- wrote:

The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.

So be sure to comment the code so people will know that you are
destroying 1NF on purpose and do not know about C/S architecture. That
might help someone maintian the code and correct it after you are gone.

There are perfectly legitimate reasons for collapsing multiple rows
into one on the back end. Perhaps you've got a presentation team that
needs all the items on a single row - to avoid expanding their result
set? You could just write a query for them (they might not be able
to), but maybe it's safer to put that into a view and support it on the
back end. Or maybe for performance reasons you put the data into an
indexed summary table using partitioning/mdc/etc based on that query.

Or maybe someone has a commercial product, or an interface to a
commercial product. And in this case they don't give you the option of
adding an entire table to describe your list well - they only give you
a single column. You'll have to make do.

But other than that, your comment was probably very valuable.

buck