Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
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?
Asked:
-
3
1 Solution
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
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.
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.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
[...]
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