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

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,

No one has any suggestions? :(

Well then, a Stored Procedure it's going to have to be (A Scalar-valued Function to be more precise). It still took me a day and a half to get it to work, but I will save you the trouble of having to look for a solution. The Scalar-valued Function looks as follows:

Code:

---

CREATE FUNCTION Concatenate(@RepairID INT)
RETURNS VARCHAR(8000)
BEGIN
    DECLARE @sReturn VARCHAR(8000)
    DECLARE @sDelimiter CHAR(2)
    SET @sDelimiter = '; '

    SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + Labor FROM laborTable
    WHERE RepairID = @RepairID ORDER BY Labor

    RETURN @sReturn
END
GO

---

After creating the function and compiling it you would add something to the effect of the following in your SQL statement or SQL Server view:

Code:

---

SELECT  RepairID, dbo.Concatenate(RepairID) AS Labor
FROM    RepairsTable

---

I am glad I have a solution, don't get me wrong (I am Happy!) But this is going to be a slight pain in the rear, since I will have to do this for each related table I want to display this way, unless I can find a way to pass the table and field name as parameters as well. I spent some time looking into this but alas all failure was upon me and I temporarily gave up. If I have time I will try to resume building a table/field neutral function. Before I sign off I would like to thank Plamen Ratchev who inspired the solution:

http://bytes.com/topic/sql-server/an...in-single-cell

I hope that this helps someone.

Hey,

I am seeing that this post is still receiving some hits. This is good. I'll be honest is saying that my co-workers and I were very excited about this solution. But as was aptly put by Roland Joffe, "There be Dragons." And indeed there are. Apparently Scalar Functions are a performance nightmare as one of my co-workers proved on a dataset of some 6000 records. I'd hate to see what would happen if you ran such a function on a dataset of a few 100,000 records (snore) So as excited as we were (major disappointment is setting in) we are back at square 2 facing the same problem. I would encourage all you DBA's out there reading this revelation to share your experiences and perhaps some recommendations. I think it would be helpful. In the meantime I will continue looking for a solution myself, and I will post my findings.
This is the post that confirmed the performance issue:
http://sqlblog.com/blogs/adam_machan...ring-post.aspx

Ok,

We are presently testing another solution using a table-valued function instead of the dreaded scalar-valued function. Based on some things I have read the performance of the table-valued function would be about 10 times faster that it's scalar counterpart. Judging from the extremely poor performance of the scalar-valued function this may still not be enough but it is better than before. So here it goes:

Code:

---

CREATE FUNCTION Concatinate(@WoID INT)
  RETURNS @Labor TABLE
  (
  -- Column(s) Returned by the function
  LaborNme nvarchar(500)
  )
  AS
  BEGIN
  -- DECLARE the return and delimiter variables
  DECLARE @sReturn VARCHAR(500)
  DECLARE @sDelimiter CHAR(2)
  SET @sDelimiter = '; '

  SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + laborName
  FROM LaborTable
  WHERE RepairID = @WoID
  -- RETURN concatinated string
  INSERT @Labor values (@sReturn)
RETURN
END

---

Since the function returns a table object we have to use a SELECT statement to call the returned value as follows:

Code:

---

SELECT RepairID,
    (SELECT LaborNme
    FROM dbo.Concatinate(RepairID) AS ConcatLabor) AS Labor
FROM RepairsTable

---

Though this looks like a suitable solution and though it appears to work (final testing will confirm or dispute the validity of this statement) I can't help but wonder if this is really the way to solve this problem. I have planned a discussion with my co-workers and fellow DBA comrades in arms and hope to shed some light on these doubts. If anyone out there has experience with this, please let us know whether or not this is the way to go about this. Any feedback, good bad and indifferent will be welcome at this point.

Thanks