Search

Custom Search

Saturday, March 8, 2008

How to Concatenate a record into a single field

Sometimes it is useful to create a column on header that contains a concatenated value of its detail. For example we want to display all available sizes of a specific product like this:
Product: Coca Cola
Sizes: 8oz, 12oz, 500ml, 1 Liter, 1.5 Liter

We can achieve that by joining the details then looping on the detail to write it on a single string. But we can concatenate it using the following:

CREATE FUNCTION Sizes
(
@ProductID varchar(50)
)
RETURNS varchar(2000)
AS
BEGIN
DECLARE @Sizes varchar(2000)

SET @Sizes=''
SELECT @Sizes=@Sizes+','+ ProductSizes.Size FROM ProductSizes
WHERE ProductSizes.ProductID=@ProductID

RETURN @Sizes = RIGHT(@Sizes,LEN(@Sizes)-1)
END

This function returns a concatenated value of sizes according to the ProductID

so we can write a query like this:

SELECT ProductID, Description, dbo.Sizes(ProductID) FROM Products

No comments:

Post a Comment

Adsense Banner