SQL Grouping Data from multiple tables.

First of all you have to create function for IssueName concatenation:

CREATE FUNCTION MyFceIssueConcat (@DocNr  varchar(100))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @IssueNames  varchar(1000)
SELECT @IssueNames = COALESCE(@IssueNames + ',', '') + CAST([IssueName] AS varchar(100))
  FROM dbo.tblDocuments
  INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID
  INNER JOIN dbo.tblDocIssues ON dbo.tblUserData.MasterGUID = dbo.tblDocIssues.DocGUID
  INNER JOIN dbo.tblIssues ON dbo.tblDocIssues.IssueGUID = dbo.tblIssues.IssueGUID
 WHERE [Doc #] = @DocNr

RETURN @IssueNames  
END

And now you may use it in the query:

SELECT [Doc #], [Beg bates], Volume, MyFceIssueConcat([Doc #]) Notes
  FROM dbo.tblDocuments 
 INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID
 INNER JOIN dbo.tblDocIssues ON dbo.tblUserData.MasterGUID = dbo.tblDocIssues.DocGUID
 INNER JOIN dbo.tblIssues ON dbo.tblDocIssues.IssueGUID = dbo.tblIssues.IssueGUID
 WHERE [IssueName] IN ('02 - Not Responsive', '01 - Responsive', 'Group Tag Test', 'Privilege')
 GROUP BY [Doc #], [Beg bates], Volume
 ORDER BY [Doc #]

Select allOpen in new window

I get an error from your code
"Msg 195, Level 15, State 10, Line 1
'test' is not a recognized built-in function name."

Where is the 'test' word used in your code? Did you add it somewhere?

i just changed your "MyFceIssueConcat" to "test".

MyFceIssueConcat is used on two places. Did you change it on both?

The first place is the function creation, the second place is the query.

Ok..i was able to run your function and "Command(s) completed successfully" but i didn't want to modify anything.. Even so..I then re-ran the query you created after the function but the results are same

Msg 195, Level 15, State 10, Line 1
'MyFceIssueConcat' is not a recognized built-in function name.

SELECT [Doc #], [Beg bates], Volume, [IssueName]
FROM (dbo.tblDocuments INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID) INNER JOIN (dbo.tblDocIssues INNER JOIN dbo.tblIssues ON dbo.tblDocIssues.IssueGUID = dbo.tblIssues.IssueGUID) ON dbo.tblUserData.MasterGUID = dbo.tblDocIssues.DocGUID
WHERE ([IssueName] = '02 - Not Responsive') OR ([IssueName] = '01 - Responsive') OR ([IssueName] = 'Group Tag Test') OR ([IssueName] = 'Privilege')
ORDER BY [Doc #]

So, please change the function call to dbo.MyFceIssueConcat():

SELECT [Doc #], [Beg bates], Volume, dbo.MyFceIssueConcat([Doc #]) Notes
  FROM dbo.tblDocuments 
 INNER JOIN dbo.tblUserData ON dbo.tblDocuments.DocGUID = dbo.tblUserData.MasterGUID
 INNER JOIN dbo.tblDocIssues ON dbo.tblUserData.MasterGUID = dbo.tblDocIssues.DocGUID
 INNER JOIN dbo.tblIssues ON dbo.tblDocIssues.IssueGUID = dbo.tblIssues.IssueGUID
 WHERE [IssueName] IN ('02 - Not Responsive', '01 - Responsive', 'Group Tag Test', 'Privilege')
 GROUP BY [Doc #], [Beg bates], Volume
 ORDER BY [Doc #]

Select allOpen in new window

I didn't want all values of Issue names associated to a doc # concatenated into one column tho. I only wanted the values that i was initially searching for.

Brilliant!! this works..just one last question..will adding and removing functions out of the database ok?

Yes, you have to create functions for this approach. To remove you may simply use

DROP FUNCTION dbo.MyFceIssueConcat

Another possibility is to use FOR XML clause as mentioned here: http://weblogs.sqlteam.com/mladenp/archive/2007/06/01/60220.aspx