select & string concatenation

Question

Post reply

select & string concatenation

Dan Sydner

SSC Journeyman

Points: 95
More actions
January 13, 2005 at 7:29 am

#162262

I stumbled upon this function in T-sql and couldn't find any documentation for it. The select statement "flattens" the t-column automatically when it returns more than one row. Even the delimeter is added to the string.

Is this totally undocumented or is it simply so that I don't know the correct name of this function?

/D (My first post pleas be gentle)

/*

Table _Test:

r t

------------

1 1

2 2

1 1

1 3

2 4

select r, dbo.flatten(r) from _Test

produces

r t

----------------

1 1, 2, 3

2 2,4

*/

create function dbo.Flatten( @r int )

returns varchar(2560)

as

begin

declare @text as varchar(2560)

declare @max-2 as varchar(255)

select top 1 @max-2 = t -- Discover the largest value of t

from _Test

where r = @r

order by t desc

set @text = ''

-- creates the return string automatically

-- no need to loop or anything

-- excludes the last value of t

select @text = @text + convert(varchar(255),t) + ', '

from _test

where r = @r

and t <> @max-2

group by t

order by t asc

-- adds the last value of t, without ',' after

set @text = @text + @max-2

return @text

end

Viewing 6 posts - 1 through 5 (of 5 total)

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

muts be a udf someone instaled for you.

I cannot find it on our (dev) sqlservers.

good hunting

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Dan Sydner SSC Journeyman Points: 95 More actions · Answer 2

Sorry for being a tad unclear. It was this feature that I was wondering about. The function was just something I made to demonstrate it. When the select returns more than one row they all are concatenated with the ',' seperating them.

select @text = @text + convert(varchar(255),t) + ', '

from _test

where r = @r

and t @max-2

group by t

order by t asc