Grouping function - 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 2005 Forums

Transact-SQL (2005)

Grouping function

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-02-04 : 05:45:23

Hi,

I want to group the following data

CREATE TABLE test(a VARCHAR(5),b VARCHAR(5),c VARCHAR(5))
INSERT INTO test(a,b,c)
SELECT '1','x','5' UNION
SELECT '1','y','6' UNION
SELECT '1','z','7' UNION
SELECT '2','a','8'

to get the following results

1,x y z,5 6 7
2,a,8

I've done this

create function dbo.concat(@a int)
returns varchar(1000)
as
begin
DECLARE @Output VARCHAR(4000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN b
ELSE @Output + ' ' + b
END
from test
where a = @a
ORDER BY b

RETURN @Output

end

create function dbo.concat2(@a int)
returns varchar(1000)
as
begin
DECLARE @Output VARCHAR(4000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN c
ELSE @Output + ' ' + c
END
from test
where a = @a
ORDER BY c

RETURN @Output

end

SELECT distinct a, dbo.concat(a),dbo.concat2(a) FROM test

However that is not very nice, I'm sure there is a more generic way of doing this.

Any help would be appreciated.

Sean

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 06:10:01

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

Sean_B
Posting Yak Master

111 Posts

Posted - 2009-02-04 : 07:49:41

Brilliant, thanks.

Sean

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources