1:m database relation "Flattened" for reporting purposes

[Bill MacLean]

--
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_Gene ral_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_Gene ral_CP1_CI_AS NOT NULL ,
[FirstNM] [varchar] (15) COLLATE SQL_Latin1_Gene ral_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_C LASS] FOREIGN KEY
(
[ClassID]
) REFERENCES [dbo].[CLASS] (
[ClassID]
),
CONSTRAINT [FK_ENROLLMENT_P ERSON] 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','Lucill e');

--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);

[Erland Sommarskog]

[posted and mailed, please reply in news]

Bill MacLean (bs**********@a tt.net) writes:

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.
Using a cursor is the only way you can to this in SQL Server reliably.
There exists a few tricks but they are not supported, and the result is not
always what you want.

From the point of view of effeciency it would be better to just select
the raw data, and do the formatting in client code.
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?

You could put the cursor thing in a table-valued function which you then
could join to other tables.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anith Sen]

You cannot do this directly. t-SQL resultsets returns tables with rows
having column, with each column representing a single value. So you will
have to hack up some kludge to get this done from the Server. Some such
ideas can be found at:
http://groups.google.com/groups?selm...TNGP09.phx.gbl

A more reliable approach is to return the resultset to the client
application & format or concatenate the way you want.

--
Anith

[Bill MacLean]

Thank you both Erland and Anith for the help. I really like method #4 on
the post that Anith linked. That method uses a table-valued function that
does not even use a cursor! The method appears to work great on the sample
Northwind database. I will adapt the idea to the dev environment for the
project here at work, and see how it scales.

I really like the idea of sparing the developers all the client side work,
so that function seems pretty neat. I'm still trying to figure out
exactly how it works, but I like the results.

Of course, I wish the concatenated output weren't necessary in the first
place, this appears to be a nice solution.

Thanks,

Bill
"Anith Sen" <an***@bizdatas olutions.com> wrote in message
news:y6******** ********@newsre ad1.news.pas.ea rthlink.net...

You cannot do this directly. t-SQL resultsets returns tables with rows
having column, with each column representing a single value. So you will
have to hack up some kludge to get this done from the Server. Some such
ideas can be found at:
http://groups.google.com/groups?selm...TNGP09.phx.gbl
A more reliable approach is to return the resultset to the client
application & format or concatenate the way you want.

--
Anith