Grouping and Collapsing rows - 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)

Grouping and Collapsing rows

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-01-18 : 17:36:30

Guys,

I have the following test table:

CREATE TABLE T(X INT, Y VARCHAR(10))

INSERT INTO T(X,Y) VALUES(1,'a')
INSERT INTO T(X,Y) VALUES(1,'b')
INSERT INTO T(X,Y) VALUES(1,'c')
INSERT INTO T(X,Y) VALUES(2,'d')
INSERT INTO T(X,Y) VALUES(2,'e')
INSERT INTO T(X,Y) VALUES(3,'f')

I would like to write a query that produces the following result, grouping and collapsing:

1 abc
2 de
3 f

Can anyone suggest how to do that?

I tried using the coalesce function, but I cannot produce an easy/straightforward query

Thanks a lot

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-01-18 : 18:16:18

I actually figured it out as such (note: table and column names changed):

CREATE Function combine(@x INT)
RETURNS VARCHAR(100)

AS

BEGIN
DECLARE @name VARCHAR(100)

SELECT @name = coalesce(@name,'') + y + ' '
FROM b
WHERE x = @x

RETURN rtrim(@name)

END

select * from b
select distinct x, dbo.combine(x)
from b

... and using dynamic sql we can parametrize the table and column names if necessary ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources