Rolling up row values from a retrieved subset into a single string

Question

Post reply

Rolling up row values from a retrieved subset into a single string

Mike Christie

SSC Rookie

Points: 38

May 6, 2004 at 12:47 am

#87139

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name

===== =========

001234 Sweater (Black)

001234 Trousers (Large)

001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

Mike

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

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

wz700 SSCertifiable Points: 7133 More actions · Answer 1

For single result, you could try something like this,

declare @res varchar(50)

set @res=''

select @res=@res+product_name + ', ' from order_item

where orderid = '001234'

select @res

If you need for eatch orderid, develop a function based on top by passing

in orderid.

Syntax are not checked.

Optimist415 Valued Member Points: 66 More actions · Answer 2

Here's one solution using a user_defined function. You may need to check it for syntax, but I think it'll be right. I also have one that doesn't use a user-defined function, so let me know if you want that one instead, but this one is simpler.

CREATE FUNCTION

dbo.StringConcat(@userID int, @fieldID int)

RETURNS VARCHAR(255)

AS

BEGIN

DECLARE @String VARCHAR(255)

SELECT @String = @String + b.product_name + ', '

from orders a, order_items b

where a.orderid = b.orderid

WHERE a.orderid = @orderID

RETURN @String

END

And here’s the call of it:

SELECT a.orderidid, dbo.StringConcat(a.orderid) as newtext

from orders a, order_items b

where a.orderid = b.orderid

GROUP BY a.orderid

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Also consider if the presenation will be handled in an application it may be simpler to put the logic in the application for the rollup than in sql.

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Thanks, Optimist 🙂 your solution was something that didn't occur to me, and is very helpful! I was looking for that right now, because I wanted to avoid the use of cursors in a very similar situation.

It works fine, and there is just one little thing that needs to be corrected in your SQL, and that is initial setting of @String to empty (instead of NULL): SELECT @String = ''. I'm not sure what the @fieldID parameter is for - I didn't use it and tested the function without it.

Thanks again!