SQL Grouping Data from multiple tables.

Need assistance on my problem below...
Tables involved = 4 (tblUserData, tblDocuments, tblDocIssues, tblIssues)
Columns involved = 4.....(Doc #, Beg bates) from tblUserData, (Volume) from tblDocuments, and (IssueName) from tblIssues.

Code is below

Issue: I would like to group by Doc # or Beg bates but would like to have certain valuesfrom the multivalue field (IssueName) into an alias field called NOTES.
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 #]

Open in new window

SQL-grouping-Data.xls
gridlineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
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 #]

Open in new window

0
gridlineAuthor Commented:
I get an error from your code
"Msg 195, Level 15, State 10, Line 1
'test' is not a recognized built-in function name."
0
pcelbaCommented:
Where is the 'test' word used in your code? Did you add it somewhere?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

gridlineAuthor Commented:
i just changed your "MyFceIssueConcat" to "test".
0
pcelbaCommented:
MyFceIssueConcat is used on two places. Did you change it on both?
0
pcelbaCommented:
The first place is the function creation, the second place is the query.
0
gridlineAuthor Commented:
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 #]
0
pcelbaCommented:
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 #]

Open in new window

0
gridlineAuthor Commented:
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.
0
pcelbaCommented:
Oh yes, it is just a small change of the function:
ALTER FUNCTION dbo.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 AND
       [IssueName] IN ('02 - Not Responsive', '01 - Responsive', 'Group Tag Test', 'Privilege')
 ORDER BY [IssueName]
 
RETURN @IssueNames  
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gridlineAuthor Commented:
Brilliant!! this works..just one last question..will adding and removing functions out of the database ok?
0
pcelbaCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.