Posted by SakyMoto on 10/02/22 11:54

Erland Sommarskog schreef:

> 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

It's a shame but we haven't yet migrated to SQL server 2005. I will try
to accomplish the result with a cursor

• Next in forum: Re: It proclaims of the Commandant in Boss Fidel Castro to the town of Cuba.
• Prev in forum: Re: Admin Version of WITH(UPDLOCK)?
• Thread view: Re: Transposing rows to columns

[Reply to this message]