Posted by Erland Sommarskog on 10/02/77 11:54

SakyMoto (jeroen.tijsen@gmail.com) writes:
> I hope someone can help me with my problem. I have searched the
> internet for days for a solution, but nothing i found seemed to work.
> The following is what i have now:
>
> appartmentid code
> 100306 Wip
> 100306 Zandbak
> 100306 Glijbaan
> 100306 Klimrek
> 100306 Schommel
> 100321 Glijbaan
> 100321 Schommel
>
> This results are made with this query:
>
> select
> appartment.appartmentid,
> listvalue.code
> from appartment
> inner join appartmentlistvalue on appartmentlistvalue.appartmentid =
> appartment.appartmentid
> inner join listvalue on appartmentlistvalue.listvalueid =
> listvalue.listvalueid
>
> The following is what i need:
>
> 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel
> 100312 Glijbaan, Schommel
>
> As you can see is this example, not all appartments have the same
> number of results. Can anyone tell me if this is possible?

On SQL 2005 you can use:

SELECT a.appartmentid,
substring(x.CodeList, 1, len(CodeList) - 1)
FROM appartment a
CROSS APPLY (select l.code + ',' AS [text()]
from appartmentlistvalue alv ond
join listvalue l on alv.listvalueid = l.listvalueid
WHERE alv.appartmentid = a.appartmenti
ORDER BY a.appartmentid
FOR XML PATH ('')) AS x(CodeList)

The syntax is a bit obscure, but it works.

On SQL 2000, you cannot do this in a single SQL statement, but you need to
run a cursor to build the lists. You may be better off to compose the
lists in the client layer.


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