Transposing rows to columns

[SakyMoto]

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.appa rtmentid,
listvalue.code
from appartment
inner join appartmentlistv alue on appartmentlistv alue.appartment id =
appartment.appa rtmentid
inner join listvalue on appartmentlistv alue.listvaluei d =
listvalue.listv alueid

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?

Many thanks,
Sakymoto

[Greg D. Moore \(Strider\)]

Re: Transposing rows to columns


"SakyMoto" <jeroen.tijsen@ gmail.comwrote in message
news:1154344089 .525844.305540@ s13g2000cwa.goo glegroups.com.. .

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:

What version of SQL are you using?

SQL 2005 has a PIVOT commandbuilt in.

Otherwise you can google for some code examples of how to do it in SQL 2000
(it ain't overly pretty.)

>
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.appa rtmentid,
listvalue.code
from appartment
inner join appartmentlistv alue on appartmentlistv alue.appartment id =
appartment.appa rtmentid
inner join listvalue on appartmentlistv alue.listvaluei d =
listvalue.listv alueid
>
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?
>
Many thanks,
Sakymoto
>

[Erland Sommarskog]

Re: Transposing rows to columns

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.appa rtmentid,
listvalue.code
from appartment
inner join appartmentlistv alue on appartmentlistv alue.appartment id =
appartment.appa rtmentid
inner join listvalue on appartmentlistv alue.listvaluei d =
listvalue.listv alueid
>
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.Cod eList, 1, len(CodeList) - 1)
FROM appartment a
CROSS APPLY (select l.code + ',' AS [text()]
from appartmentlistv alue alv ond
join listvalue l on alv.listvalueid = l.listvalueid
WHERE alv.appartmenti d = 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@sommarsk og.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

[SakyMoto]

Re: Transposing rows to columns


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.appa rtmentid,
listvalue.code
from appartment
inner join appartmentlistv alue on appartmentlistv alue.appartment id =
appartment.appa rtmentid
inner join listvalue on appartmentlistv alue.listvaluei d =
listvalue.listv alueid

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.Cod eList, 1, len(CodeList) - 1)
FROM appartment a
CROSS APPLY (select l.code + ',' AS [text()]
from appartmentlistv alue alv ond
join listvalue l on alv.listvalueid = l.listvalueid
WHERE alv.appartmenti d = 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@sommarsk og.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/prodinf...ons/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

[Bryan]

Re: Transposing rows to columns

Hi,

Here is an example that may meet your needs. It's a bit rough but it
does work.


USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrder Products(@Order ID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Outpu t+', ', '') + CONVERT(varchar (20),
P.ProductName)
FROM dbo.[Order Details] OD
JOIN dbo.Products P
ON P.ProductID = OD.ProductID
WHERE OD.OrderID = @OrderID
ORDER BY P.ProductName

RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrder Products(OrderI D)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrder Products
GO


thanks

Bryan



SakyMoto wrote:

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.appa rtmentid,
listvalue.code
from appartment
inner join appartmentlistv alue on appartmentlistv alue.appartment id =
appartment.appa rtmentid
inner join listvalue on appartmentlistv alue.listvaluei d =
listvalue.listv alueid
>
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?
>
Many thanks,
Sakymoto

[Erland Sommarskog]

Re: Transposing rows to columns

Bryan (bryanmcguire@b tinternet.com) writes:

Here is an example that may meet your needs. It's a bit rough but it
does work.
>
>
USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrder Products(@Order ID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Outpu t+', ', '') + CONVERT(varchar (20),
P.ProductName)
FROM dbo.[Order Details] OD
JOIN dbo.Products P
ON P.ProductID = OD.ProductID
WHERE OD.OrderID = @OrderID
ORDER BY P.ProductName

Nah, it seems to work, but there is not really any guarantee. It relies
on undefined behaviour, and you may get the result you expect.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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