[RESOLVED] Creating a single view to capture related data in a single field

[Working.Net]

WARNING! The proposed solution has serious performance ramifications (See post #4.) I am looking into an alternative, and will post the result when I find it.

Ok, I know, this one is going to seem weird and it will break all the official rules of database development but I don't know how else to go about it.

Let me explain. I have a query builder tool in ASP.NET (see below) that allows a user to build the WHERE portion of a query. The query is based on a view (table) that is selected by the user. The user selects field names as well, from a DropDownList which is populated based on the selected table name. The whole thing works pretty slick, and it allows the user to build simple queries themselves. The view (table) are in fact SQL Server views. This offers the added benefit of allowing the client (person paying for the work) to add views (tables) to the ASP.NET Query builder tool himself, something he really likes.

The client however would like to incorporate data from related tables. Being that the view, on which the query is based, and the view, on which the resulting report is based, are identical, if I were to just create a join between the main table and related tables it would create records for each occurrence of the relationship. Thus a record with 3 related records would be displayed 3 times in the report. This is something we don't want.

I thought that if I could create a view of the main record and concatenate the key elements, like person names, of the related table into a single field I could query the data using a LIKE operator, and it would return a single record rather than copies of the records. Thus a query results like:

Record 1; Data 1; Bill
Record 1; Data 1; Joe
Record 1; Data 1; Pete
Record 2; Data 2; Bill
Record 2; Data 2; Sandy

Would end up looking something like this:
Record 1; Data 1; Bill, Joe, Pete
Record 2; Data 2; Bill, Sandy

I have done this in stored Porcs and ASP.NET using for loops. I suppose I can still use stored Procs but I would like to see if I can just do it in a view using straight SQL first. Anyone have any idea how to do this?

Thanks,