Posted by Erland Sommarskog on 12/03/05 12:42

bika (aesahab@gmail.com) writes:
> I have a table that has values as follows:
> PersonID Degree
> 55 MD
> 55 Phd
> 55 RN
> 60 MD
> 60 Phd
>
> I need a create a query that will give me output like this:
>
> PersonID Degree
> 55 MD, Phd, RN
> 60 MD, Phd

If you are on SQL 2000, you will have to run a cursor. There is no defined
way to produce this result set. (There are some undefined ways which may
work, but I would not recommend to rely on.)

If you are on SQL 2005, this is possible thanks to the improved XML support.
I got this example from an SQL Server developer:

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

I have not really grasped how it works, but it works. :-)

--
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

[Back to original message]