Creating CSV Lists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating CSV Lists

Author

Topic

ackweb
Yak Posting Veteran

54 Posts

Posted - 2005-04-23 : 19:54:29

I'm using the following query to generate a CSV list of the donors for a particular item in my database:

DECLARE @ExampleCSV varchar(400)
DECLARE @ItemID int

SET @ItemID = 25

SELECT @ExampleCSV = (CASE ISNULL(@ExampleCSV,'') WHEN '' THEN DonorName ELSE @ExampleCSV + ', ' + DonorName END) FROM tblDonorList WHERE ItemID = @ItemID

SELECT @ItemID AS ItemID, @ExampleCSV AS Donors

The example results for this query are as follows:

ItemID ---- Donors
25 -------- Bob Smith, Joe Green

Is there a way to generate a similar csv list of donors by item for all the items?

ItemID ---- Donors
25 -------- Bob Smith, Joe Green
26 -------- Peggy Brown, Will Trump

The only thing that I've come up with is to use a User Defined Function and return the CSV donor list for each item incrementally. However, this is painfully slow when there is a long list of items. I'd appreciate any guidance on this.

Kristen
Test

22859 Posts

Posted - 2005-04-24 : 02:58:18

Can't think of a smart way off hand, but your example could be a little slicker (although i don't know if it would make it faster)

SELECT @ExampleCSV = NULL -- Not required if @ExampleCSV has been DECLARED but not used
SELECT @ExampleCSV = COALESCE(@ExampleCSV + ', ', '') + DonorName
FROM tblDonorList
WHERE ItemID = @ItemID

Kristen

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2005-04-24 : 06:22:16

This is by far the most amazing solution I have seen for your question: http://www.sqlteam.com/item.asp?ItemID=11021

Nobody really knows why this works, but who cares, as long as it works!

The only other solution i can think of is using a UDF, but as you have seen from your own experience, they can be quite slow with larger sets.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources