469,317 Members | 1,924 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

Combine column multiple rows into one row

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.

Dec 7 '05 #1
5 36009
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
Dec 7 '05 #2
Thanks Knut.

Dec 7 '05 #3
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
Dec 8 '05 #4
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.

Dec 8 '05 #5
--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

Dec 9 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by RotterdamStudents | last post: by
2 posts views Thread by laurenq uantrell | last post: by
1 post views Thread by Bob Loveshade | last post: by
2 posts views Thread by Michael | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.