You are here: Re: Transposing rows to columns « MsSQL Server « IT news, forums, messages
Re: Transposing rows to columns

Posted by Bryan on 08/03/06 15:23

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация