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

[unknown]

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, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by 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.
ID
--
R1
R2
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.
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
RETURNS varchar(8000) AS
BEGIN
SELECT Name
FROM SubTable
ORDER BY Name
OPEN NameCursor
BEGIN
END
CLOSE NameCursor
DEALLOCATE NameCursor
END
Is there a better way of doing this?
Thanks,
Rob

[unknown]

And http://blog.csdn.net/hery2002/archive/2008/04/13/2288347.aspx .
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>

Post by unknown
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by 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.
ID
--
R1
R2
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.
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
RETURNS varchar(8000) AS
BEGIN
SELECT Name
FROM SubTable
ORDER BY Name
OPEN NameCursor
BEGIN
END
CLOSE NameCursor
DEALLOCATE NameCursor
END
Is there a better way of doing this?
Thanks,
Rob

[Robert Morley]

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


Rob

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