473,835 Members | 1,754 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 38322
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
33315
by: RotterdamStudents | last post by:
Hello there, i have a strange problem. I can't get php to insert multiple rows at once in a MySQL database. I use the $sql = "INSERT INTO database (a,b,c,d,e) VALUES ('$a', '$b' ,'$c', '$d', '$e')"; I want to insert 5 rows at a time in the database, but it only inserts every 5th record. For example: 1. AA
1
9731
by: Valerie | last post by:
Hi everyone, I really appreciate if anyone could help me with this tricky problem that I'm having. I'm looking for a sample script to combine data in multiple rows into one row. I'm using sqlserver. This is how data is stored in the table. ID Color 111 Blue 111 Yellow
2
6116
by: laurenq uantrell | last post by:
IS there a way to combine all matching rows in a table so that it outputs as one row, for example: tblMyStuff UniqueID int IDENTITY ParentID int SomeSuch nvarchar(50) SomeSuch2 nvarchar(50) Table data:
1
3359
by: Bob Loveshade | last post by:
I am looking for an example that shows how to select and highlight multiple rows in a DataGrid. My DataGrid is part of a Web User Control which is contained in an ASPX page. I haven't been able to find any examples which demonstrate how to do this. My Code:
11
2564
by: UDBDBA | last post by:
Hi: This is a merge questions which has been posted and answered... in my case need more clairification when target table (tableB) matched multiple rows to be updated based on the ON condition for a single row from source table (tableA). There is a one to many relationship from source to target table (because of the ON condition). We also know upfront that thsi merge will result in all update and no inserts. The source table is tableA...
7
43147
by: Mintyman | last post by:
Hi, I'm working on a system migration and I need to combine data from multiple rows (with the same ID) into one comma separated string. This is how the data is at the moment: Company_ID Material 0x00C00000000053B86 Lead 0x00C00000000053B86 Sulphur 0x00C00000000053B86 Concrete
18
4016
by: Apple001 | last post by:
Hi all! I am having trouble with joining multiple rows into one row. I will appreciate any help. For columns in the query, I have: invID(autot number), entryDate, invDate, vendor, invoiceAmount from table named tblInvoice, and building, account, percent (allocation in percentage for each building) from table named tblAllocation. This is how the table looks like: InvID / entryDate / invDate / vendor / invoiceAmount / building /...
1
2400
by: Sanjaylml | last post by:
I have a form through which, e-mail address of parties are showing through datasheet mode. Is their any way to club the data of multiple rows in one column? Like : ajaimathur@rediffmail.com amarnathmishra@rediffmail.com To
2
5343
by: Michael | last post by:
It seems that a gridview allows us to delete only a single row at a time. How to extend this functionality to select multiple rows and delete all of the selected rows in a single stroke? just like what hotmail web UI is doing now (having the option of selecting multiple rows (using the checkbox provided) and perform a set of operations on them)
0
9808
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10812
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10561
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10235
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7766
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5638
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5804
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3995
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3089
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.