SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using COALESCE to Build Comma-Delimited String
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/30/2001 :  11:24:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 02/01/2001 :  08:48:16  Show Profile  Reply with Quote
Using COALESCE to Build Comma-Delimited String

I do would like your comments about this article and the thread

A bug in ORDER BY ???

from microsoft.public.sqlserver.programming.


a29379@my-deja.com

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 02/01/2001 :  09:35:42  Show Profile  Visit graz's Homepage  Reply with Quote
Comment on the thread

Based on my quick reading and my experience, if you don't use an order by you can't guarantee any order for the result set.

I think it's as simple as that. You talk about inconsistent results. If you don't use an ORDER BY you can't predict the order the rows will be returned. It's as simple as that.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/07/2001 :  12:57:28  Show Profile  Reply with Quote
Use NULLIF instead

Following the code in the article, the results I get using COALESCE include an unwanted comma + space at the beginning of the final string:

... COALESCE(@EmployeeList + ', ', '') + CAST(Emp_UniqueID AS varchar(5)) ... gives

, 1, 2, 4

I think NULLIF is what you want:

DECLARE @theList varchar(100)

SELECT @theList = NULLIF(@theList + ', ', ', ') + StatusID
FROM StatusAssoc
WHERE AccountID = 79

SELECT @theList

================
A, B, C

NULLIF returns NULL if the first value equals the second value. So, the final string is pieced together like this:

Step 1. NULL +
Step 2. + ', ' +
Step 3. + ', ' +

Unless I'm understanding wrong, it seems you need to use NULLIF instead of COALESCE. Regardless, this is a great tip.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/08/2001 :  11:12:23  Show Profile  Reply with Quote
Don't use a function at all!

I think using a function like COALESCE or ISNULL to accomplish this is nonsense. There's no need for it, and since the function is only needed on the very first 'pass', including it in the select will decrease performance for every row past the first one. To prevent the concatenation from resulting in a NULL value, simply set the @EmployeeList to an empty string before running the select. After the select, cut the first comma to complete the list:

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = ''

SELECT @EmployeeList = @EmployeeList + ', ' + CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
SELECT @EmployeeList = SUBSTRING(@EmployeeList, 3, 100)

Simple, and no call to COALESCE for each row, just one call to SUBSTRING for the whole batch.

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/13/2001 :  17:18:28  Show Profile  Reply with Quote
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

Any ideas?

Thanks

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/21/2001 :  23:44:26  Show Profile  Reply with Quote
Comma delimited list for each sales call (#6)

Yup,to get the string for each sales call in the database is the rub.
You can do this easily with the RAC (replacement for Access crosstab)
xtab and pivoting utility (see the @colretain parameter).
Check out the v1.50 beta for s2k at:
www.angelfire.com/ny4/rac/

Go to Top of Page

mvallani
Starting Member

Canada
1 Posts

Posted - 03/27/2002 :  19:16:36  Show Profile  Reply with Quote
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




Go to Top of Page

bob s
Starting Member

United Kingdom
7 Posts

Posted - 08/06/2002 :  23:19:11  Show Profile  Reply with Quote
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

Go to Top of Page

markah_1999@yahoo.com
Starting Member

USA
1 Posts

Posted - 10/09/2006 :  16:00:55  Show Profile  Reply with Quote
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...
Go to Top of Page

Rukmang
Starting Member

1 Posts

Posted - 04/06/2007 :  06:49:36  Show Profile  Visit Rukmang's Homepage  Reply with Quote
Can you please let me know the usefulness of third argument ('')
in the coalesce statement.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 04/06/2007 :  07:51:23  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 04/06/2007 :  11:17:27  Show Profile  Reply with Quote
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
Go to Top of Page

samwise_007
Starting Member

3 Posts

Posted - 04/09/2007 :  12:48:38  Show Profile  Reply with Quote
>>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.
Go to Top of Page

samwise_007
Starting Member

3 Posts

Posted - 04/09/2007 :  12:51:56  Show Profile  Reply with Quote
With coalesce, the second param is required(the value to return if null) - so it says if @variable is null, return 'nothing'.
Go to Top of Page

ritush
Starting Member

2 Posts

Posted - 09/25/2007 :  03:13:12  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/25/2007 :  03:21:31  Show Profile  Reply with Quote
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
Go to Top of Page

ritush
Starting Member

2 Posts

Posted - 09/25/2007 :  03:30:34  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/25/2007 :  05:15:44  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/25/2007 :  06:09:35  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/25/2007 :  08:19:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000