How to show data from the 'many' side in one row
-
Say I have a Product table with ProductID and ProductDescription.
And I have a Colour table with ColourID and Colour Description and a ProductColour table.
So, Product table might have:
ProductID ProductDescription
2427 10cm x 20cm plastic box
The Colour table might have:
ColourID Colour Description
1 Red
2 Blue
3 Green
4 Yellow
5 Purple
The ProductColour table might have
ProductID ColourID
2427 1
2427 2
2427 4
Which means ProductID 2427 is available in Red, Blue and Yellow
If I run a query like ...
SELECT ProductID, ProductDescription, Colour
FROM Product INNER JOIN
ProductColour ON Product.ProductID = ProductColour.ProductID INNER JOIN
Colour ON ProductColour.ColourID = Colour.ColourID
I will get data like:
2427 10cm x 20cm plastic box Red
2427 10cm x 20cm plastic box Blue
2427 10cm x 20cm plastic box Yellow
But, what I want to get back is just one row like this ...
2427 10cm x 20cm plastic box Red, Blue, Yellow
How can you concatenate the data from the many side of a relationship so it can be returned in one row?
Thanks for any help.
-
SELECT p.ProductID,p.ProductDescription,
STUFF((SELECT ','+c.Colour AS "data()"
FROM Colour c
INNER JOIN ProductColour pc ON pc.ColourID=c.ColourID
AND pc.ProductID=p.ProductID
ORDER BY c.ColourID
FOR XML PATH('')),1,1,'') AS Colours
FROM Product p
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi, thanks for your reply.
I get a syntax error though ...
Incorrect syntax near the keyword 'FOR'.
... but can't work out what I am doing wrong.
Cheers.
-
Are you running SQL Server 2005?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
I believe that's a 2005 only construct. If you are running 2000, you posted in the wrong place. Let us know and we'll help.
In 2000, there's no good way to do this. If you know how many (max) colors there are, you can write multiple joins to handle this. Or you can loop through. This is really a client thing and you'd be better off handling this on the client side.
-
The way I've handled this in 2000 (and 2005) is to use a User Defined Function to string the text together. You have to be careful about using this technique since it is hidden RBAR (Row By Agonizing Row). It has to run basically a sub-query for each row evaluated in order to string each product's color.
Here's the query:
SELECT
ProductID
, ProductDescription
, dbo.fnStringColors(ProductID) AS Colors
FROM
Product
Here's the function to call:
CREATE FUNCTION fnStringColors
(
@ProductID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE
@Color VARCHAR(MAX)
, @Comma VARCHAR(1)
SET @Color = ''
SET @Comma = ''
SELECT @Color = @Comma + @Color + X.Color, @Comma = ','
FROM
(SELECT PC.Color
FROM ProductColour PC
INNER JOIN Color C ON
PC.ColourID = C.ColourID
WHERE PC.ProductID = @ProductID
) AS X
RETURN @Color
END
-
Actually, I made a typo in the above function. It should read SELECT C.Colour. PC is the alias for ProductColour, not the Colour table.
Todd Fifield
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply