Grouping data into a single field

[Robert Morley]

Okay, for presentation purposes in my Access project, I've got a few
different views that use functions to group fields from different records
together in the same field in a view.

For example:

TopLevelTable:

ID
--
R1
R2


SubTable:

ID Name
-- ----
R1 Rob
R1 Chris
R1 Dave
R2 Bob
R2 Jill

....would become

ID Names
-- -----
R1 Chris; Dave; Rob
R2 Bob; Jill

So far, my method has been to create a UDF which uses a cursor to loop
through the appropriate field data, concatenate, and return a string.

The top-level query looks something like this:

SELECT ID, dbo.MyUDF(ID)
FROM TopLevelTable

....with MyUDF looking like this (sorry, this is necessarily being retyped,
as it's on a different computer, and I'm renaming things on the fly for
simplicity/clarity, so please forgive any minor inconsistencies):

CREATE FUNCTION dbo.MyUDF(@ID char(2))
RETURNS varchar(8000) AS
BEGIN
DECLARE @Name varchar(100)
DECLARE @RetVal varchar(8000)
DECLARE @NameCursor CURSOR LOCAL FOR
SELECT Name
FROM SubTable
WHERE ID = @ID
ORDER BY Name

SET @RetVal = ''
OPEN NameCursor
FETCH NEXT FROM NameCurosr INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @RetVal = @RetVal + '; ' + @Name
FETCH NEXT FROM NameCursor INTO @Name
END
CLOSE NameCursor
DEALLOCATE NameCursor

RETURN SUBSTRING(@RetVal, 3, 8000)
END

Is there a better way of doing this?


Thanks,
Rob

 

[Sylvain Lafontaine]

You can take a look at the following article from Anith Sen:
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e

 

[Sylvain Lafontaine]

And http://blog.csdn.net/hery2002/archive/2008/04/13/2288347.aspx .

 

[Robert Morley]

Thanks, Sylvain, I'll take a look at those.


Rob