471,601 Members | 1,083 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,601 software developers and data experts.

Join multiple records into one field

Is there a way to create one field from multiple records using sql.
For example

Table 1
John 18
Peter 18
David 18

Now I want an sql query that when executed will return a field that
looks like this

Query1
John Peter David

So basically it will return one record with all the name in one field

May 4 '06 #1
4 9851
Here is the answer for your question

select a.column1,
(select column2 from table where column1=a.column1),
(select column2 from table where column1=a.column1),
(select column2 from table where column1=a.column1)
from Table1 a

You need to have a single column in your table which can build
recursive relation.

May 4 '06 #2
Thanks for replying but I am getting the following error "single-row
subquery returns more than one row "

May 4 '06 #3
Terren (ca**************@gmail.com) writes:
Is there a way to create one field from multiple records using sql.
For example

Table 1
John 18
Peter 18
David 18

Now I want an sql query that when executed will return a field that
looks like this

Query1
John Peter David

So basically it will return one record with all the name in one field


If you are on SQL 2000, you need to run a cursor over the table. There
is no defined method that gives you the result in a single statement.
You do things like SELECT @x = @x + col FORM tbl, but the correct result of
this operation is undefined, so you cannot be sure on what you get.

On SQL 2005, you can use the XML stuff to get there. Here is a
sample query for the Northwind database:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by StealthBananaT | last post: by
reply views Thread by Petre Agenbag | last post: by
6 posts views Thread by Christopher Harrison | last post: by
7 posts views Thread by Pino | last post: by
9 posts views Thread by Alan Lane | last post: by
2 posts views Thread by Darryl Kerkeslager | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by MichaelMortimer | last post: by
reply views Thread by CCCYYYY | last post: by

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.