• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

Help with comma delimited sql column in resultset

I'm beginning to wonder if this is even possible.  Here's what I'm trying to do.

Let's say I have two tables, customers and orders.

Customers
--------------
CustId   Name
------------------------
1          John Doe
2          Jim Smith
3          Joey Tribiani

Orders
---------------
OrderId         CustId       Product Name
---------------------------------------------------
1                    1            toothbrush
2                    1            toothpaste
3                    1            floss
4                    2            computer
5                    2            keyboard
6                    2            mouse
7                    3            tv
8                    3            dvd player
9                    3            vcr


What I want is a list of customers, with a comma separated field of all of the products they have purchased, like below:

1     Jon Doe                   toothbrush,toothpaste,floss
2     Jim Smith                computer,keyboard,mouse
3     Joey Tribiani            tv,dvdplayer,vcr

I know I can do this in a user defined function by passing in a customer id, looping through the products and returning the comma separated string.  But I'm trying to find a universal way to do this so that I wouldn't need a udf for every instance.  I think ideally and aggregate function would be a good solution, but SQL 2000 doesn't allow user-defined aggregate functions.

Any ideas?
0
npond
Asked:
npond
  • 3
1 Solution
 
chaniewskimCommented:
Create a function parametrized for CustId variable:

[...]

DECLARE @tmp varchar(2000)
select @tmp = COALESCE(@tmp + ',' + [Product Name], [Product Name]) from Orders WHERE CustId=@CustId
RETURN @tmp


And then use

SELECT CustId, Name, YourFunction(CustId) FROM Customers
0
 
npondAuthor Commented:
chaniewskim,

I am looking for a more universal way to do this, your solution would require different functions for every instance I wanted to do this.  For example, in the future I might want to use this to get a list of products with each having a comma separated field of customers who purchased that product.  I wouldn't want to have to write a new function to do that.

Nathan
0
 
chaniewskimCommented:
It's ugly, but you could use a dynamic query in a string, and run it using EXECUTE. This way you could make a function with parameters like

@table
@field
@selectCondition

then

DECLARE @tmp varchar(2000)
DECLARE @sql varchar(2000)
SET @sql = 'select @tmp = COALESCE(@tmp + '','' + ' + @field + ', ' + @field + ') from ' + @table + ' WHERE ' + @selectCondition
EXEC (@sql)
RETURN @tmp
0
 
Scott PletcherSenior DBACommented:
You might want to consider sticking with custom functions.  

AFAIK the only way to make it generic is to use dynamic SQL, but you can't use dynamic SQL in a function.  You can in a SP, but then you would have to cursor thru the main table to be able to call the SP for every row -- very, very, very bad for performance.  A cursor should be a very last resort.
0
 
chaniewskimCommented:
whoops
your're right Scott
so, only custom functions
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now