Combining results in one string

I have a table as follows:
SizeID | Description
1 Extra Small
2 Small
3 Medium
4 Large
5 Extra Large
And then a Product_Size relationship table:
ProductID | SizeID
1000 1
1001 3
1001 4
1001 5
1010 2
1010 3
I want to be able to write a query which returns the size descriptions in
one single string, separated by commas.
For example, if I request for Product #1001, I want the return string to be:
'Medium, Large, Extra Large'
Please show me how this query can be written.
Thanks!You can do this by creating function.
CREATE FUNCTION dbo.GetSize(@.ProductId int)
RETURNS varchar(50)
AS
Begin
Declare @.ReturnValue Varchar(500), @.descripton Varchar(50)
Set @.ReturnValue = ''
Declare ProductSize Cursor
For Select Description
From Product_Size, SizeMaster
Where Product_Size.SizeID = SizeMaster .SizeID
AND ProductId = @.ProductId
Open ProductSize
Fetch Next From ProductSize into @.descripton
While @.@.Fetch_status = 0
Begin
SET @.ReturnValue = @.ReturnValue + @.descripton + ','
Fetch Next From ProductSize into @.descripton
End
Set @.ReturnValue = left(@.ReturnValue,Len(@.ReturnValue)-1)
Close ProductSize
Deallocate ProductSize
Return(@.ReturnValue)
End
Select Distinct ProductId, dbo.GetSize(productID) from Product_Size
Thanks
Baiju
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
> I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>|||I made a simillar request yesterday. check the post titled "Concating Values
of a Column based on a Group"
Gopi
"Uncle Ben" <spamfree@.nospam.com> wrote in message
news:OO1L1kGJFHA.2356@.TK2MSFTNGP12.phx.gbl...
>I have a table as follows:
> SizeID | Description
> 1 Extra Small
> 2 Small
> 3 Medium
> 4 Large
> 5 Extra Large
> And then a Product_Size relationship table:
> ProductID | SizeID
> 1000 1
> 1001 3
> 1001 4
> 1001 5
> 1010 2
> 1010 3
> I want to be able to write a query which returns the size descriptions in
> one single string, separated by commas.
> For example, if I request for Product #1001, I want the return string to
> be:
> 'Medium, Large, Extra Large'
> Please show me how this query can be written.
> Thanks!
>