Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Grouping Data from multiple tables.

Posted on 2009-07-15
12
Medium Priority
?
159 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 43

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 43

Expert Comment

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

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

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

Expert Comment

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

Expert Comment

by:pcelba
ID: 24864566
The first place is the function creation, the second place is the query.
0
 

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 43

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 43

Accepted Solution

by:
pcelba earned 1500 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 43

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

604 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