Article: Using COALESCE to Build Comma-Delimited String

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

Site Related Forums

Article Discussion

Article: Using COALESCE to Build Comma-Delimited String

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-01-30 : 11:24:34

Garth is back with another article. This one talks about building a comma-separated value string for use in HTML SELECT tags. It's also handy anytime you need to turn multiple records into a CSV field. It's a little longer and has some HTML but a good read.

Article Link.

mvallani
Starting Member

1 Post

Posted - 2002-03-27 : 19:16:36

Anyone have any ideas on the previous post:

Comma delimited list for each sales call
Ok, that's great, it gets a comma separated string if you know the sales call id you want. how about getting the string for each sales call in the database e.g.

call id string
------- -------
1 1, 2, 4
2 2

Thanks

bob s
Starting Member

7 Posts

Posted - 2002-08-06 : 23:19:11

Hi,

Any ideas how to return all records when they are likely to exceed the datalength applied to the parameter (I am trying to return a string list of all email addresses from a table - am using varchar (8000) as can't use text with '+')

Thanks

markah_1999@yahoo.com
Starting Member

1 Post

Posted - 2006-10-09 : 16:00:55

using coalesce() in a loop in a tsql sp only works if
1) set the list var = NULL each pass
2) list var is declared varchar

the 1st makes sense. Not sure bout the 2nd...

Rukmang
Starting Member

1 Post

Posted - 2007-04-06 : 06:49:36

Can you please let me know the usefulness of third argument ('')
in the coalesce statement.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-04-06 : 07:51:23

COALESCE can accept any number of parameters. It returns the first parameter that isn't null.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-06 : 11:17:27

quote:
Originally posted by Rukmang

Can you please let me know the usefulness of third argument ('')
in the coalesce statement.

Did you even look at Books Online (the product documentation)?!
http://msdn2.microsoft.com/en-us/library/ms190349.aspx

samwise_007
Starting Member

3 Posts

Posted - 2007-04-09 : 12:48:38

>>Can you please let me know the usefulness of third argument ('')
>>in the coalesce statement.

There is no third statement, the first comma is in quotes. I was wondering the same thing, since a hardcoded value in the second param would mean coalesce never reaches the third.

samwise_007
Starting Member

3 Posts

Posted - 2007-04-09 : 12:51:56

With coalesce, the second param is required(the value to return if null) - so it says if @variable is null, return 'nothing'.

ritush
Starting Member

2 Posts

Posted - 2007-09-25 : 03:13:12

Hi, am using following query to generate a comma-seperated list of the personnumbers. There are about 3,35,000 records in the column for which the csv is generated. The query works fine if the size of varchar is defined as 100 but then the most obvious problem with that is: it doesn't generate the csv for all 3,50,000 records. As a part of workaround, I defined the varchar to have the max size but now the problem is: there is a serious performance hit and the query is taking more than 20mins and is still executing...I'm kind of stuck here as I don't know the workaround for this. This is very critical for my project and any help on this regard will be highly appreciated. following is the query that am using:

DECLARE @PersonNumber varchar(max)
SELECT PersonnelNumber = COALESCE(PersonnelNumber + ', ', '') +
CAST(PersonnelNumber AS varchar(100))
FROM person
select @personnelnumber

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 03:21:31

If you have got thousands of values to be concatenated you need to do that at the front end, not in SQL Server, I reckon

ritush
Starting Member

2 Posts

Posted - 2007-09-25 : 03:30:34

I don't have a fron-end in this case... everything is on back-end... any workaround that we can have for the backend itself?? The database is running on SqlServer2005

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 05:15:44

I doubt there is a workaround.

That sort of string concatenation is doing:

Allocate 4 bytes of memory
Store this 4 byte string.
Allocate 8 bytes of memory
Move original 4 bytes
Append this 4 bytes
Allocate 12 bytes of memory
...
and at row 335,000:
Allocate 1,340,000 bytes of memory
Move original 1,339,996 bytes
Append this 4 bytes

its a huge amount of memory management.

If you could use a pre-allocate string and fixed length for the individual values you might be able to use STUFF() more effectively.

But really this is not something that SQL Server is going to be good at, and it would be better handled "somewhere else"

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 06:09:35

If "everything is back-end", why do you need to concatenate the personnelnumbers at all?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 08:19:41

quote:
Originally posted by Peso

If "everything is back-end", why do you need to concatenate the personnelnumbers at all?

E 12°55'05.25"
N 56°04'39.16"

OP wants to see the result in Query Analyser

Madhivanan

Failing to plan is Planning to fail

sqlwayne
Starting Member

1 Post

Posted - 2007-10-02 : 19:21:58

Very cool code, just what I was looking for! I pulled it into an ASP page for reporting election results.

bitburner
Starting Member

1 Post

Posted - 2008-05-13 : 16:33:01

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

If "everything is back-end", why do you need to concatenate the personnelnumbers at all?

E 12°55'05.25"
N 56°04'39.16"

OP wants to see the result in Query Analyser

Madhivanan

Failing to plan is Planning to fail

Well the problem in your case is the number of string concats that take place when doing this. So the solution is to reduce the number of concatenation operations by using a temp table.

I know this is a big ugly monster and normally I wouldn't recommend it to anyone

- but for what you need (just seeing the stuff in QA) this will work a lot faster; I was able to run it in about 6 1/2 minutes versus your 20+ doing it without the cursor and temp table on a Core 2 Duo Laptop with 2 GB of RAM).

CREATE TABLE #TempColTable (ConVal nvarchar(max))

DECLARE collation_csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT
[Value]
FROM
TestTable

OPEN collation_csr

DECLARE @insertSection as nvarchar(max)
DECLARE @numberPlaceHolder as nvarchar(10)
DECLARE @rowCounter as bigint;

FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;

SET @insertSection = @numberPlaceHolder + ', ';
SET @rowCounter = 0;

WHILE(@@FETCH_STATUS = 0)
BEGIN

FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;

SET @insertSection = @insertSection + @numberPlaceHolder + ', ';

IF(@rowCounter = 7500)
BEGIN
INSERT INTO #TempColTable (ConVal) VALUES (@insertSection);
SET @insertSection = '';
SET @rowCounter = 0; --reset the counter so we can insert this batch and move on
END
ELSE
BEGIN
SET @rowCounter = @rowCounter + 1;
END
END

CLOSE collation_csr
DEALLOCATE collation_csr

DECLARE @insertedRecords as bigint;
DECLARE @number varchar(max)

SELECT @insertedRecords = COUNT(ConVal) FROM #TempColTable

IF(@insertedRecords = 0)
BEGIN
SET @number = @insertSection;
END
ELSE
BEGIN
SELECT
@number = COALESCE(@number + ', ', '') + [ConVal]
FROM
#TempColTable
END

DROP TABLE #TempColTable

SELECT @number;

SELECT LEN(@number);

rgerald
Starting Member

2 Posts

Posted - 2008-09-10 : 17:11:18

CAVEAT: This technique fails for me when the total number of characters in the variable is more than 255 (even when the variable is declared as varchar(8000)).

The final SELECT statement only shows the first 255 characters. I am using SQL Server (2000). Is that an issue?

Rand

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 17:24:25

You only get to SEE the first 255 characters in Query Analyzer, because that is the default setting.
You can change this number of characters in Tools -> Option menu.

E 12°55'05.63"
N 56°04'39.26"

rgerald
Starting Member

2 Posts

Posted - 2008-09-10 : 17:31:02

One more bit:

The problem I just described only applies to "output to text" or "output to file" - those cut off at 255 characters.

If you use "output to grid", the full 8000 characters will be there for a varchar(8000) variable.

Rand

Piyush
Starting Member

8 Posts

Posted - 2011-07-25 : 02:17:07

Its a nice article.
If you want to see further more, then just follow the link:
Working with NULLS in SQL Server - PART 2
unspammed

Piyush Bajaj

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources