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

Many thanks,
Sakymoto

[Greg D. Moore \(Strider\)]

"SakyMoto" <je***********@gmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.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.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?

Many thanks,
Sakymoto

[Erland Sommarskog]

SakyMoto (je***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SakyMoto]

Erland Sommarskog schreef:

SakyMoto (je***********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/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]

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.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + 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.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
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.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?

Many thanks,
Sakymoto

[Erland Sommarskog]

Bryan (br**********@btinternet.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.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SELECT @Output = COALESCE(@Output+', ', '') + 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx