With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!
Course Of The Month
8 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Help with comma delimited sql column in resultset
Posted on 2004-04-21
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
7 Comments
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
[...]
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
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
Active 1 day ago
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 20 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.