Writing out a one column result set as a concatenated string

Question

Writing out a one column result set as a concatenated string

kurtyue

SSC Enthusiast

Points: 132
More actions
February 18, 2009 at 8:59 am

#205747

Hi, I have the following scenario. Let's say I have a table with
ProductID (int)
ProductDescription (varchar(50))
ProductCategoryID (int)
Now I want to select all the ProductDescription for a given ProductCategoryID but I want it to display in a single cell. So instead of
select ProductDescription
from dbo.Products
where ProductCategoryID = 1
giving me the following:
ProductDescription
--------------------
Hammer
Wrench
Pliers
Screwdriver
Instead I want one calculated field that would display this
ListOfProducts
---------------------
Hammer, Wrench, Pliers, Screwdriver
Is there a way to do this? The number of products per category would be arbitrary and I would not know it before hand.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Sure...read this[/url] and this[/url].

These two articles will show you exactly how it's done and you will almost certainly learn other useful tricks along the way.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kurtyue SSC Enthusiast Points: 132 More actions · Answer 2

kurtyue

SSC Enthusiast

Points: 132

February 18, 2009 at 9:20 am

#946057

great, thanks!

kurtyue SSC Enthusiast Points: 132 More actions · Answer 3

Thanks for the links Chris. I read over the two articles and both of them only deal with numeric fields where a SUM() and a GROUP BY can be applied to aggregate the data. My problem is that my field is VARCHAR. Again, I'm looking to be able to convert a dataset of arbitrary size such as

ProductDescription

------------------

Hammer

Wrench

.

Pliers

Screwdriver

To one single field (concatenated string) of

ListOfProducts

------------------

Hammer, Wrench, ... , Pliers, Screwdriver

Any further help on this would be greatly appreciated.

kurtyue SSC Enthusiast Points: 132 More actions · Answer 4

I've decided to RBAR for now because I'm under a time crunch. 🙁

Still open to suggestions though. Thanks!

Lowell SSC Guru Points: 323518 More actions · Answer 5

here's an example for you to look at:

stuff all values into a column

------------DDL----------------

create table test (id int identity, category varchar(100),

name varchar(100), allnames varchar(8000) null)

insert test (category, name)

select 'fruit', 'apple' union

select 'fruit', 'pear' union

select 'fruit', 'orange' union

select 'meat' , 'beef' union

select 'meat' , 'pork' union

select 'meat' , 'gammon'

------------------------------------

declare @category varchar(20), @name varchar(20)

--select @name ='' ,@category =category from test where id = 1

update t

set @name = case when @category = category then @name +','+name else name end , allnames = @name, @category = category

from test t

update test

set allnames = x.allnames

from test t

join (select max(allnames)allnames, category from test group by category)x

on x.category = t.category

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kurtyue SSC Enthusiast Points: 132 More actions · Answer 6

kurtyue

SSC Enthusiast

Points: 132

February 18, 2009 at 2:33 pm

#946347

That worked beautifully. Thanks!