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)
 String Aggregate Functions

Author  Topic 

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-21 : 12:09:35
Hi,

Is it possible to have an aggregate function concatenate all the values in a character column into one long string?

Cheers,

X.Factor

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-21 : 13:33:19
Not in an aggregate function, but you can do it in a stored procedure -- subject to the 8000 character limit of local variables..

DECLARE @MYSTRING varchar(8000)
SET @MyString = ''

SELECT @MyString = @MyString + MyColumn FROM MyTable
Go to Top of Page

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-24 : 17:40:36
You can do it in MySQL with GROUP_CONCAT!
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-03-24 : 17:46:55
That's super, in MS SQL you can do referential integrity. Which do you choose ?


Damian
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-24 : 19:53:49
Here is a GROUP_CONCAT udf for SQL Server. This particular udf is table dependent (test_group_concat) but it offers the same functionality as MySQL's extension.

I used the DDL from the example of the MySQL group_concat() example at the following link:
http://perso.numericable.fr/~karemman/freeware/MyGroupConcat/Readme.htm
CREATE TABLE test_group_concat(id INTEGER NOT NULL identity(0,1), code VARCHAR(8), label VARCHAR(255) )

INSERT INTO test_group_concat( code, label) VALUES( 'A01', '0LINE 1 LABEL')
INSERT INTO test_group_concat( code, label) VALUES( 'A011', 'LINE 2 LABEL')
INSERT INTO test_group_concat( code, label) VALUES( 'A0111', 'LINE 3 LABEL')
INSERT INTO test_group_concat( code, label) VALUES( 'A02', '0LINE 1 LABEL 2')
INSERT INTO test_group_concat( code, label) VALUES( 'A021', 'LINE 2 LABEL 2')
INSERT INTO test_group_concat( code, label) VALUES( 'B01', '0LINE 1 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B011', 'LINE 2 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B0111', 'LINE 3 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B0111', 'LINE 4 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B0111', 'LINE 5 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B0111', 'LINE 6 LABEL 3')
INSERT INTO test_group_concat( code, label) VALUES( 'B02', '0LINE 1 LABEL 4 (nulls)')
INSERT INTO test_group_concat( code, label) VALUES( 'B021', NULL)
INSERT INTO test_group_concat( code, label) VALUES( 'B0211', NULL)
INSERT INTO test_group_concat( code, label) VALUES( 'B0211', 'LINE 3 LABEL 4 (nulls)')
INSERT INTO test_group_concat( code, label) VALUES( 'B0211', 'LINE 4 LABEL 4 (nulls)')
INSERT INTO test_group_concat( code, label) VALUES( 'B0211', 'LINE 5 LABEL 4 (nulls)')
INSERT INTO test_group_concat( code, label) VALUES( 'B0211', 'LINE 6 LABEL 4 (nulls)')

go
create function dbo.GROUP_CONCAT(@i varchar(100), @seperator varchar(100), @start int, @end int)
returns varchar(8000)
as
begin

declare @String varchar(8000)
declare @store table (id int identity(1,1), code varchar(8), label varchar(8000))

insert into @store (code, label)
Select left(code,3), label
from test_group_concat
where left(code,3) = @i
order by id asc

select @String = label + @seperator + @String
from @store
where ((@start is null) or (id >= @start)) and ((@end is null) or (id < @end))
order by id desc

return ltrim(@String)
end
go

set concat_null_yields_null off
select left(code,3) code, dbo.GROUP_CONCAT(left(code,3), null,null,null) label
from test_group_concat
group by left(code,3),dbo.GROUP_CONCAT(left(code,3),null,null,null)

select * from test_group_concat

drop function dbo.GROUP_CONCAT
drop table test_group_concat
Go to Top of Page
   

- Advertisement -