Article Link.

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

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.

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.

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.

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

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/