Simple but tricky String Concatenation issue

Question

Post reply

Simple but tricky String Concatenation issue

Justin-14543

SSCrazy

Points: 2017
More actions
August 31, 2007 at 6:41 am

#180747

I have two columns Country and CompanyName. Table looks like this:
Country              CompanyName
-----------------------------------
UK                    ABC Company
UK                    XYZ Company
UK                    SQL Company
USA                   Microsoft
USA                   Oracle
Result Expected:
----------------
UK                   ABC Company, XYZ Company, SQL Company
USA                  Microsoft, Oracle
How can I achieve this? I tried the following query:
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ', ' + companyname from northwind..customers where country = 'UK' group by country, companyname
print @sql
But when I do this, it errors out:
declare @sql varchar(8000)
set @sql = ''
Select 'UK',
(select @sql = @sql + ', ' + companyname from northwind..customers where country = 'UK' group by country, companyname)
print @sql
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 1

Sorry, but what is your question?

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

Justin-14543 SSCrazy Points: 2017 More actions · Answer 2

Sorry, I fat fingered the question. I edited the post. Can you see the question now? Thanks.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 3

Have a look at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=391111

Peter posts there a very nice and fast solution that uses XML PATH.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 4

The other solution would be to write a custom aggregate using CLR code and then you could just select your aggregate function with a group by country. By doing this, you could then create a list of ANY column grouped by any other column.

If the custom aggregate was called COMMALIST, the select would then sa

select Country, CommaList(CompanyName) from <table> group by CompanyName

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 5

If you go for the CLR aggregate, the CLR aggregate may get its input in any order depending on how the query optimizer decides. Concatenation is not commutative Your results can be different between two executions of the query. The solution using xml path orders the items in the list, and it also seems to be the faster.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Justin-14543 SSCrazy Points: 2017 More actions · Answer 6

XML Path works great in SQL 2005. But right now, I need it in SQL 2000. Thanks.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 7

The user defined function in the first post on http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=391111

can do it on 2000.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Justin-14543 SSCrazy Points: 2017 More actions · Answer 8

UDF works but very slow. So, I believe just got to live with it. Thanks everybody.

Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.