Solved

SQL Grouping Data from multiple tables.

Posted on 2009-07-15
12
150 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:gridline
  • 7
  • 5
12 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24863836
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
 

Author Comment

by:gridline
ID: 24864417
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24864493
Where is the 'test' word used in your code? Did you add it somewhere?
0
 

Author Comment

by:gridline
ID: 24864527
i just changed your "MyFceIssueConcat" to "test".
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24864536
MyFceIssueConcat is used on two places. Did you change it on both?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24864566
The first place is the function creation, the second place is the query.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:gridline
ID: 24864613
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
 
LVL 41

Expert Comment

by:pcelba
ID: 24864661
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
 

Author Comment

by:gridline
ID: 24864715
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
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24864844
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
 

Author Closing Comment

by:gridline
ID: 31603912
Brilliant!! this works..just one last question..will adding and removing functions out of the database ok?
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24871741
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL syntax error in VBA 11 32
query question 13 41
Has anyone used domo? 1 39
Trouble connecting to SqlServer database 4 31
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now