ORDER BY in Coalesce function

[twifosp]

I've written a function to coalesce mutliple rows into one:

ALTER FUNCTION [dbo].[CombineRepNames] 
(
	@CustomerNumber varchar(50)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @VAR varchar(1000)
set @VAR = ''
SELECT  @VAR = coalesce(@VAR + ' , ','') + name 
FROM	valignmentmaster
WHERE	soldtocustomer = @CustomerNumber
AND		productdivision = 'crm'
AND		status = 'active'
AND		REPCODE NOT LIKE '%910%'
--order by name asc
 
RETURN RIGHT(@VAR, LEN(@VAR)-3)

This code works fine and brings back all of the names to one column.
However when I uncomment out the order by statement, it only brings back one name.

I have a need to have the list alphabatized so i can compare them. Anyone know why this does not work?

 

[gmmastros]

try this:

ALTER FUNCTION [dbo].[CombineRepNames] 
(
    @CustomerNumber varchar(50)
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @VAR varchar(1000)
set @VAR = ''
SELECT  @VAR = coalesce(@VAR + ' , ','') + name 
FROM    valignmentmaster
WHERE    soldtocustomer = @CustomerNumber
AND        productdivision = 'crm'
AND        status = 'active'
AND        REPCODE NOT LIKE '%910%'
[!]AND        Name > ''[/!]
order by name asc
 
RETURN RIGHT(@VAR, LEN(@VAR)-3)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

What is your SQL Server version? The ORDER BY approach and this custom function, unfortunately, not guaranteed to work 100%. If you're using later version of SQL Server, use XML approach for concatenating data. You also will not need a scalar function which is performance bog.

Take a look at the following blogs to learn more:

MSDN thread about concatenating rows

Making a list and checking it twice

Concatenating Rows - Part 1

Concatenating Rows - Part 2


PluralSight Learning Library

 

[twifosp]

@gmmastros -- Nice thought, but none of the records are empty.

@markros -- Thanks for the tip. Based on your first link I'll switch to the for xml path. Works faster too, as you said.

SELECT 	soldtocustomer, stuff((select  ',' + Name
from	valignmentmaster a1
where	a1.soldtocustomer = a.soldtocustomer
order by name for xml path('')),1,1,'') a
from	valignmentmaster a

I need to brush up on this stuff function, I'm not entirely sure what it does. For XML is pretty self explanatory when you don't use the function.

 

[twifosp]

Oh, well that's easy enough. It stuffs charecters in a string. Sometimes it is that easy.