1:m Database Relation Flattened For Reporting Purposes

--
Example Schema posted at end of message:
---
For reporting purposes, I need to build a single comma delimited string of
values from the "many" table
of a database.

The easiest way to see what I want is to look at the sample after my
signature.
By the way, this is actually a busines problem, not homework! I just
created a simple
example using class and persons because everyone is familiar with that
relationship.

I have two tables on the 'one' side of the relationship: PERSON and CLASS
The ENROLLMENT table resolves the many to many relationship between PERSON
and CLASS.
(I know that a real system would be date effective, etc, but this is just a
simple example.
that will show my problem). ENROLLMENT has one row for each Class in which a
Person is enrolled.

Look at the sample report: I have to "flatten" the join result and list
the class titles in a
comma delimited string. I am stuck with this reporting requirement, and I
am NOT going to denormalize
the tables.

One way to accomplish the result is to use a cursor to step through the rows
and build a "Classes"
string with concatenation. I don't much like this option. I am not writing
the front end code,
but I want to make it easy for the developer. Ideally, I would like to give
him a flattened view
so he can just do a simple join and run his report.

I believe that what I want cannot be accomplished with ANSI SQL. However,
does MS SQL have some
extensions that could help me do the job? Failing that, how could I write a
stored procedure that would
return the personID and the "Classes" string in a format that would be
joinable to the other tables?


Thanks,


Bill MacLean

P.S. Some people like to see actual database scripts as samples instead of
a textual representation.
I have pasted in a script that creates sample tables and populates them.

--Sample Tables and Reports:


TABLE PERSON
PersonID LastNM FirstNM
--------- ----------- ---------
1 Smith John
2 Jones Sara
3 Smith Lucille


TABLE CLASS
ClassID ClassNM
----------- ------------------
10 SQL Server 101
20 C++
25 Object Oriented Design
40 Inorganic Chemistry
50 Organic Chemistry
80 Early Lit.

TABLE ENROLLMENT
PersonID ClassID
-------- ---------
1 10
2 10
1 40
1 80
3 20
3 25

SAMPLE REPORT
Person ID Name Classes
--------- ----------------------- ------------------------------------
-----------------------------
1 Smith, John SQL Server 101, Inorganic Chemistry,
Early Lit.
2 Jones, Sara SQL Server 101
3 Smith, Lucille C++, Object Oriented Design


/************************************************** ********
SQL Server Script
/************************************************** ********/

CREATE TABLE [dbo].[CLASS] (
[ClassID] [int] NOT NULL ,
[ClassNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ENROLLMENT] (
[PersonID] [int] NOT NULL ,
[ClassID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PERSON] (
[PersonID] [int] NOT NULL ,
[LastNM] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstNM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CLASS] WITH NOCHECK ADD
CONSTRAINT [PK_CLASS] PRIMARY KEY CLUSTERED
(
[ClassID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ENROLLMENT] WITH NOCHECK ADD
CONSTRAINT [PK_ENROLLMENT] PRIMARY KEY CLUSTERED
(
[PersonID],
[ClassID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PERSON] WITH NOCHECK ADD
CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED
(
[PersonID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ENROLLMENT] ADD
CONSTRAINT [FK_ENROLLMENT_CLASS] FOREIGN KEY
(
[ClassID]
) REFERENCES [dbo].[CLASS] (
[ClassID]
),
CONSTRAINT [FK_ENROLLMENT_PERSON] FOREIGN KEY
(
[PersonID]
) REFERENCES [dbo].[PERSON] (
[PersonID]
)
GO

-- Insert rwo for each CLASS
INSERT INTO CLASS VALUES (10,'SQL Server 101');
INSERT INTO CLASS VALUES (20,'C++');
INSERT INTO CLASS VALUES (25,'Object Oriented Design');
INSERT INTO CLASS VALUES (40,'Inorganic Chemistry');
INSERT INTO CLASS VALUES (50,'Organic Chemistry');
INSERT INTO CLASS VALUES (80,'Early Lit.');

-- Insert row for each PERSON
INSERT INTO PERSON VALUES (1, 'Smitn','John');
INSERT INTO PERSON VALUES (2, 'Jones','Sara');
INSERT INTO PERSON VALUES (3, 'Smith','Lucille');

--Insert row for each ENROLLMENT
INSERT INTO ENROLLMENT VALUES (1,10);
INSERT INTO ENROLLMENT VALUES (1,40);
INSERT INTO ENROLLMENT VALUES (1,80);
INSERT INTO ENROLLMENT VALUES (2,10);
INSERT INTO ENROLLMENT VALUES (3,20);
INSERT INTO ENROLLMENT VALUES (3,25);