Solved

Help with comma delimited sql column in resultset

Posted on 2004-04-21
7
512 Views
Last Modified: 2012-05-04
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
Comment
Question by:npond
[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
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10880907
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
 

Author Comment

by:npond
ID: 10881251
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
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10881345
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 10881364
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
 
LVL 5

Expert Comment

by:chaniewskim
ID: 10881540
whoops
your're right Scott
so, only custom functions
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question