SQL Function - concatenate strings? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Function - concatenate strings?

Author

Topic

SQLDan9
Starting Member

10 Posts

Posted - 2002-05-31 : 07:56:03

For example (more complex in real db), tableA

ID String
1 nb
1 ns
1 ne
2 on
3 ab

Would it be possible to group by ID and concatenate the strings into one and to obtain the following result (note the ascending order of the concatenated string):

ID Concatenated
3 ab
1 nb ne ns
2 on

Damn these clients with weird requirements :)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-31 : 08:22:45

There's a few threads that have code for this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14095
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978

One or more of them can be modified to assemble the strings in the proper order.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 08:49:10

This concept came from one of the rockstars on this board . . . I'm searching for the thread so I can give props, but here's the code in the meantime . . .


/*
create table sqldan9 (id int, string char(2))
insert sqldan9
select 1,'nb' union
select 1,'ns' union
select 1,'ne' union
select 2,'on' union
select 3,'ab'
*/

declare @list varchar(8000), @lastid int
select @list = '', @lastid = -1

select
    [id],
    string,
    convert(varchar(8000),'') as list
into
    #result
from
    sqldan9
order by
    [id], string

update
    #result
set
    @list = list = case
                       when @lastid <> [id] then string + '' --what?
                       else @list + ' ' + string
                   end,
    @lastid = [id]

select
    [id],
    max(list) as list
from
    #result
group by
    [id]

drop table #result
go

edit: wow, sniped by 20 minutes . . . gotta learn to refresh....
EDIT2: Anybody wanna help me figure out why I had to put the +'' up by the 'what?' comment?....doesn't work without it....
<O>

Edited by - Page47 on 05/31/2002 08:58:19

sanju
Starting Member

4 Posts

Posted - 2002-05-31 : 09:14:29

It may not be required if the column definition of string is varchar(2) instead of char(2)

--------------------------------------------------------------------
EDIT2: Anybody wanna help me figure out why I had to put the +'' up by the 'what?' comment?....doesn't work without it....
--------------------------------------------------------------------

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 09:14:33

interesting.....

if I change the data type of column string in sqldan2 to a varchar(2), I don't need to do the silly + '' in list assignment. Why would datatype make a difference ?


/*
drop table sqldan9
create table sqldan9 ([id] int, string varchar(2))
insert sqldan9
select 1,'nb' union
select 1,'ns' union
select 1,'ne' union
select 2,'on' union
select 3,'ab'
*/

declare @list varchar(8000), @lastid int
select @list = '', @lastid = -1

select
    [id],
    string,
    convert(varchar(8000),'') as list
into
    #result
from
    sqldan9
order by
    [id], string

update
    #result
set
    @list = list = case
                       when @lastid <> [id] then string
                       else @list + ' ' + string
                   end,
    @lastid = [id]

select
    [id],
    max(list) as list
from
    #result
group by
    [id]

drop table #result
go

EDIT: DAMN IT, SNIPED AGAIN!!

<O>

Edited by - Page47 on 05/31/2002 09:15:11

sanju
Starting Member

4 Posts

Posted - 2002-05-31 : 09:17:10

If you dont want to change the datatype to varchar then i think you have to use the trim functions.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 09:25:30

quote:

If you dont want to change the datatype to varchar then i think you have to use the trim functions.

can you explain in a bit more detail? trim what? nothing is getting assigned too the string column... what am I missing

<O>

sanju
Starting Member

4 Posts

Posted - 2002-05-31 : 09:37:44

This is something unique to columns of char datatype. Rtrim function on char column will do the trick. Your update statement should look like this if string column is defined as char(2)
------------------------------------------------------
update
#result
set
@list = list = case
when @lastid <> [id] then rtrim(string) --+ '' --what?
else @list + ' ' + string
end,
@lastid = [id]
---------------------------------------

If string column is declared as varchar(2) then rtrim(string) is not required. I have no definite technical reasoning to this behaviour.

SQLDan9
Starting Member

10 Posts

Posted - 2002-05-31 : 09:53:38

Let me guess, the code supplied in robvolk's site should be in a stored procedure or something? I'm using Cold Fusion as a front end to my SQL Server 2000.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 10:18:34

quote:
Let me guess, the code supplied in robvolk's site should be in a stored procedure or something?

yeah, something like that. I have a hard time thinking of reasons why any tsql code should be embedded in your cf code.....

More on the wierd char/varchar thing (NOTE: is is more academic than practical, but I always like to try to figure out these wierd things) ... set ansi_padding off allows the char typed string column to function properly in the @variable = column = expression syntax. I suppose this is because the #result temp table gets defined a bit differently (that is TrimTrailingBlanks is set to no). I'm still a bit miffed, cause there are no trailing blanks to trim, and regardless, why would that prevent the column assingment from working correctly? Looking for Fribble or Chadmat or nr or RobVolk or any of the big

s to help sort this out . . . or call it a bug.

<O>

SQLDan9
Starting Member

10 Posts

Posted - 2002-05-31 : 11:14:20

I finally got it to work... in a regular CFQUERY tag. Forgot that in CF you have to add double pound signs for singles. Second, I didn't include SET NOCOUNT ON and OFF during the TSQL. Once I did this, It worked. However, I have no clue as to how it's doing the looping to concatenate the strings.

I know it's here though (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978)

"SELECT @Keywords = @Keywords = Coalesce(@Keywords + ', ', '') +
Keyword FROM Keywords
WHERE PhotoID = (SELECT PhotoID FROM #PhotoTemp WHERE TempID = @iRow)"

Edited by - SQLDan9 on 05/31/2002 11:16:02

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 11:53:35

I feel like a nut having this conversation with myself....

During the @list = list = expression, for the first record, the string column is assigned to list. However, because of how the temp table is defined with regart do TrimTrailingBlanks, setting the varchar col (list) equal to the string actually results in the two character of the string + enuff trailing spaces to fill the column. (strange, right?!?). Therefore on the next pass, the @list + ' ' + string gets trunced cause list column is already 'full'.

So it all comes down to the TrimTrailingBlanks setting which you only see in sp_help and nowhere else. You can't explicilty set it. And its not datatype specific. For example, in the select ... into the convert(varchar(8000),'') as list results in TrimTrailingBlanks=no for the col in the temp table. However, space(8000) as list, while defining the column as the same varchar(8000) results in TrimTrailingBlanks=yes for the col. Still strange....

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q296559 partially explains that this is 'expected behaviour' assuming select...into under the hood is actually create table() alter table add column blah, column blah etc.....

This ends my exploration into strange trivial sql things for today ... hold me mommy, I'm scared

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources