SQL String Concatenation
-
Hi,
I have the following Data
AddressID, TradeName
2, ABB
2, ACC
2, AEE
3, AAA
3, BBB
4, AAA
I would like to write a sql statement the returns the data in the following format :-
AddressID, TradeNames
2, ABB ACC AEE
3, AAA BBB
4, AAA
I have tried many ways, but cannot get it to work, any help would be appreciated.
Many Thanks
Andy
-
I have posted what I hope is a solution to your problem below. But if someone knows a dirrerent solution (without loops and, if possible, without functions), please post it...
create table tradeTable(AddressID int, TradeName varchar(3))
go
insert into tradeTable select 2, 'ABB'
insert into tradeTable select 2, 'ACC'
insert into tradeTable select 2, 'AEE'
insert into tradeTable select 3, 'AAA'
insert into tradeTable select 3, 'BBB'
insert into tradeTable select 4, 'AAA'
go
create function addStrings
(
@AdressId int
)
returns varchar(8000)
as
begin
declare @STR varchar(8000)
select @STR = ''
select @STR = @STR + TradeName + ' ' from tradeTable where AddressID = @AdressId
order by TradeName
return @STR
end
go
select AddressID, dbo.addStrings(AddressID) from tradeTable group by AddressID
-
Jesper,
Thanks for the quick response, that has solved my problem for now, but I would also like to know if it can be done without a function. I had got it working earlier with a loop, but you suggestion is a lot better.
Thanks
Andy
-
In this case, the function is the 'loop'.
The function just encapsulates the 'looping logic' needed to pivot the tradenames.
Though, be aware that (afaik) this 'pivot-trick' isn't supported, and if memory serves me, there may be situations where the pivoting doesn't work as expected. Apart from that, I do use this technique in some places, but it's better to point out that you do it on your own risk.
/Kenneth
-
I didn't know that it wasn't supported....
It would be great if there were a built-in sum function for strings (like the sum function for numbers), but maybe it doesn't exist because the order of the strings matter (unlike the sum function for numbers).If anyone knows more about the possible failure of this 'pivot-trick', please tell...
-
I think the main problems comes from the fact that the string can only be 8000 characters long and that's hard to check while concatenating.
-
Just calculate sum(len()) before you start, and if that's more than 8000, don't do it
-
In SQL Server 2005 we will have PIVOT.
I don't remember the specifics about when this trick didn't work, it had something to do with indexes, that much I know. But, as I said, I use it myself in a couple of places, and I haven't had any problems with it (yet)
/Kenneth
-
Nope, that was not it - that much I remember
Anyway, I just mentioned it as a 'standard disclaimer' - something to keep in the back of your mind when you use this stuff.
/Kenneth
-
I was reffering to that syntax that I thaught didn't work :
Declare @Var as varchar(8000)
set @Var = ''
Select @Var = @Var + name + ', ' from dbo.SysObjects where len(@Var) < 1000
Select @Var, len(@Var)
I had already tried to do something like this but the where condition didn't work. Maybe it was a bad cached plan or unexpected cast operations???
-
ummm maybe.. dunno.. was very long time ago. This 'magic pivot' has been around since 6.x days. I only remember that once there was an 'issue' when doing it, and I did also find what caused it at that time, though I can't recall anything more than it had something to do with clustered indexes and (I think) nullability.. Don't even remember which SQL Server version it was, so it's not even sure that the 'issue' is an issue any more
/Kenneth
-
This is probably because the where condition is evaluated before, and not while, the select is executed. I think, however, that the following could be what you want (referring to the function that I posted above):
create function addStrings
(
@AdressId int
)
returns varchar(8000)
as
begin
declare @STR varchar(8000)
select @STR = ''
select @STR = @STR + case when len(@str) < 1000 then TradeName + ' ' else '' end from tradeTable where AddressID = @AdressId
order by TradeName
return @STR
end
go
-
Not that was not the problem... In my case the where was simply ignored. I had a where len() < 1000 and the string built up to 8K anyways.
-
Just a reminder:
The WHERE clause is the FIRST evaluated in a SELECT statement therefore that condition will return true and wont be evaluated every time
To force Row by Row Processing you need to use a Function!
Cheers!
* Noel -
It might very well be that, but I didn't keep the original code so it's hard to pinpoint the problem after the fact. But thanx again for the info.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply