npond
asked on
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,flos
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?
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,flos
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?
ASKER
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
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
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
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
whoops
your're right Scott
so, only custom functions
your're right Scott
so, only custom functions
[...]
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