August 15, 2007 at 6:26 pm
I have a qeury which pulls all customers with duplicate addresses and displays a concatenated, comma delimited list of those customers last names at that address. It successfully uses a UDF to do it. Is it possible to do the same thing with a correlated subquery instead of the UDF? Here are the query and function.
select count(CustomerId) as CustId, PropertyAddress1 as Address,
dbo.GetCustomerNamesConcat(PropertyAddress1) as LastNames
from Customers
group by PropertyAddress1
having count(CustomerId) > 1
CREATE FUNCTION GetCustomerNamesConcatByAddress (@address varchar(50))
RETURNS varchar(150)
AS
BEGIN
DECLARE @lastnames varchar(500), @delimiter char
SET @delimiter = ','
SELECT @lastnames = COALESCE(@lastnames + @delimiter, '') + ci.CustomerLastName
from Customers ci where PropertyAddress1 = @address
RETURN(@lastnames)
END
Thank you for any help you can give.
Susan
August 16, 2007 at 1:43 am
There is no aggregate function provided by SQL Server that would concatenate the fields, so you will not be able to do this with a correlated subquery. Basically for every row in a single group that has the same PropertyAddress1 you will need to reduce the result to a single filed. Aggregates, like COUNT, SUM, STDEV, do this. Note that aggregates should be deterministic, their input is a set, and sets are not ordered. In your example the function reduces a certain number of rows into filed, but its input depends on the order of the internal select statement. The result may be different after you add data to your table. I.e. you may get once the result 'Jones,Smith,Wood', another time 'Wood,Jones,Smith'. This nondeterminism is basically the reason for the lack of a concatenation aggregator.
What you can do, is to use CLR. You can write a CLR Aggregator that can concatenate the items for you. This would behave similarly to the COUNT aggregator you are already using.
Note, that you could make your aggregation deterministic by specifying ordering in the user defined function you are using. However, if you decide to use a CLR aggregator, you will not be able to do this, and the result will, as it does now, depend on the query optimizer and the physical location of your data.
Regards,
Andras
August 16, 2007 at 9:34 am
Thank you very much for your reply. The explanation helps. I knew that there wasn't a concatenating aggregate, which is why I wrote the function, but I thought that I might be able to combine them into a single query. Perhaps the fact that I had to use a variable in the query made that impossible. Thanks for the info about CLR. I'll think that I'll stick with my function for now.
Regards,
Susan
August 16, 2007 at 1:58 pm
If you are using SQL Server 2005, using FOR XML PATH is faster
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
N 56°04'39.16"
E 12°55'05.25"
August 17, 2007 at 2:22 am
Peter's solution is indeed impressive. It does seem to be faster, and unlike the CLR solution, but like the user defined function, it allows ordering the items that are to be concatenated, and can remove duplicates.
Regards,
Andras
August 18, 2007 at 3:32 pm
Thanks! FOR XML PATH is exactly what I was looking for. Here is the new solution using FOR XML PATH rather than the user defined function.
select count(CustomerId) as Number, PropertyAddress1 as Address,
stuff((
select ',' + c.CustomerLastName
from Customers c
where c.PropertyAddress1 = Customers.PropertyAddress1
for xml path('')
),1,1,'') as CustList
from Customers
group by PropertyAddress1
having count(CustomerId) > 1
Regards,
Susan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply