Grouping data into a single field

  • Thread starter Thread starter Robert Morley
  • Start date Start date
R

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
 
Back
Top