column concat - SQL Server Forums

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)

column concat

Author

Topic

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2004-08-24 : 18:48:51

I noticed in one of spirits posts the following sql:


declare @ColumnList varchar(500)
select @ColumnList = COALESCE(@ColumnList + ';', '') + Col1
from Table1
select @ColumnList

Wow! It seems there is a cool way to do string concat
(ie: take a field from every row and concat them together with a
supplied separator - excellent!)

Now how do I do this without a variable?

create table #t_test (n int, s sysname)
insert into @t_test select 0, 'a'
insert into @t_test select 0, 'b'
insert into @t_test select 0, 'c'
insert into @t_test select 1, 'x'
insert into @t_test select 1, 'y'
insert into @t_test select 1, 'z'

I want to:
select n, concat(s,':')
from #t_test

to get result:
0, 'a:b:c:'
1, 'x:y:z:'

I have a complex sql-statement that does this, but it doesn't use
COALESCE. So I'm hoping to get a good optimization.

Thanks in advance,
J

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 18:55:16

[code]

CREATE FUNCTION udf_Convert_ToCSV
(@n int)
RETURNS varchar(4000)
AS

BEGIN

DECLARE @s varchar(4000)

SELECT @s = COALESCE(@s + ':', '') + s
FROM t_test
WHERE n = @n

RETURN (@s)

END

GO

create table t_test (n int, s sysname)
insert into t_test select 0, 'a'
insert into t_test select 0, 'b'
insert into t_test select 0, 'c'
insert into t_test select 1, 'x'
insert into t_test select 1, 'y'
insert into t_test select 1, 'z'

SELECT DISTINCT n, dbo.udf_Convert_ToCSV(n) AS s
FROM t_test

drop function udf_Convert_ToCSV
drop table t_test

[/code]

Tara

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2004-08-24 : 19:16:29

That is very nice Tara, unfortuantly I failed to mention we still support sqlserver7
(ie: No functions).

I know I can do this in a loop and I'll test the speed of doing that,
however I was hoping for a magic use of COALESCE that would be 'concat'

Thanks anyway,
J

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 19:23:50

For 7.0: http://www.sqlteam.com/item.asp?ItemID=11021

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources