|
|
Posted by Erland Sommarskog on 05/05/06 01:01
Terren (calmdownnumber12@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|