Posted by Erland Sommarskog on 03/08/07 22:46

Steve London (sylondon@optonline.net) writes:
> Let's say I have 2 tables related:
>
> Owner:
> ---------
> o_id
> o_name
>
> Dog:
> ---------
> d_id
> d_name
> o_id - for Owner table.
>
> If the data is laid out as
>
> o_id o_name
> 1 John
>
> d_id d_name o_id
> 1 Skippy 1
> 2 Fido 1
>
> How can I make a query that will produce the following results:
>
> o_id o_name owned dog names
> 1 John Skippy, Fido
>
> I think it has something to do with unions but I can't seem to get it.
> I'm using SQL Server Compact Edition.

There isn't any straight-forward way of doing this. It is possible do this
one statement with a quirk of XML as in this example:

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 don't know if all this syntax is available, in Compact Edition, though.
(While called SQL Server, it's an entirely different engine.)

Else you will have run a cursor to achieve this result.




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