Returning data from multiple rows in one row

Question

Returning data from multiple rows in one row

BrianBurgit

SSChasing Mays

Points: 650
More actions
December 22, 2006 at 3:06 pm

#122087

I have a Table like this

ProductID
ProductName

I have a second Table that represents the various colors a product comes in, like this

ProductID
Color

I would like to write a query that returns the ProductName and all of the colors it comes in, separated by commas, like this

ProductName               Color
Lamp                           Red, Blue
Table                            Brown, Black, Grey

How do I do this?

Thanks,

Brian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

shashi kant SSCommitted Points: 1659 More actions · Answer 1

Dear BrianBurgit check this i hope this code really help you.

there are two option you have for each product id . first you have to use function and other is you have to use cursor in your stored procedure for each productid.

first you have to declare two variable. if you pass a product id either using cursor or using function.

here is a code using cursor.

DECLARE @Result VARCHAR(800), @productid int

DECLARE @CUR AS CURSOR

SET @cur = CURSOR SCROLL FOR

SELECT DISTINCT PRODUCTID FROM Table1

open @cur

Fetch next from @cur into @productid

while @@fetch_Status =0

begin

SELECT @Result= COALESCE(@Result+',','') + color

FROM @table2

WHERE productid = @productid

ORDER BY productid

Select @Productid 'Product ID', @result 'Color'

fetch next from @cur into @productid

end

close @cur

Deallocate @cur

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Check out this message it uses the function approach

I would strongly urge you not to use the cursor.

Plus this can be used inline with a query.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=331770#bm331823

BrianBurgit SSChasing Mays Points: 650 More actions · Answer 3

Thanks Ray, I was looking to avoid a cursor, works great.