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

[Back to original message]