?
Solved

SQL Join Query Help

Posted on 2013-11-04
6
Medium Priority
?
440 Views
Last Modified: 2013-12-05
I have a Training_Tasks table that looks like this:
ID  TaskDept   TaskName
01  Eng            JobPrep
02  Eng            JobCheck
03  Mill           BeamSaw
04  Mill           Cutting

and an Employee table that looks like this:
ID  Dept  Name
01  Eng    Mike
02  Eng    Paul
03  Mill    John

and the table to store my data for Task Training Hours looks like this:

ID  EmpID  TaskID   TotalHours   TrainingDate
01  01         01          1.5                  1/1/2013
02  01         04           6.5                 1/1/2013
03  02         02           8.0                  1/1/2013

... and so on...

I need a view the above table and see my Task Training Hours represented this way:
(see task name)

EmpDept   Employee     TaskDept   JobPrep     JobCheck    Cutting     TrainingDate
Eng            Mike                  Eng           1.5                                               1/1/2013
Eng            Mike                  Mill                                              6.5            1/1/2013
Eng            Paul                   Eng                             8.0                              1/1/2013


Basically I want a column split out per TaskName and the view needs to be editable.

Thank you for your help!
0
Comment
Question by:Bianca
[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
6 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 39621793
interesting puzzle there
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39621856
That would be a PIVOT, where rows are rows, data in one column becomes the columns on the return set, and the 'cell' intersect is an aggregate like SUM() or Count().

Click on the below link, and scroll down, for decent examples.
http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
0
 
LVL 4

Expert Comment

by:Ramdasa Mayya
ID: 39621953
You can use crosstab
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

Author Comment

by:Bianca
ID: 39621956
I need the SQL view reflected that way. SQL Query...
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39622810
You have 7 columns in the expected result but the expected result has 5 columns of data only. How exactly you want the result set?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39698626
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

771 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