select & string concatenation

Question

Post reply

select & string concatenation

Dan Sydner

SSC Enthusiast

Points: 105
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:
rt
------------
11
22
11
13
24
select r, dbo.flatten(r) from _Test
produces
rt
----------------
11, 2, 3
22,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: 135369 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 Enthusiast Points: 105 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

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 3

Nothing in ours either matey.....

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

No, I'm afraid, but this is a known behaviour. It will only work up to 8,000 characters and is a well-known trick to flatten a table.

Watch out for this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;287515

SQL Server MVP Adam Machanic has also a cool UDF with this technique posted here: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dan Sydner SSC Enthusiast Points: 105 More actions · Answer 5

Aggregate concatenation... it's good to know what you are doing. So a big thank you for the links! And to the rest of for taking the time.