Solved

SQL Join Query Help

Posted on 2013-11-04
6
425 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
6 Comments
 
LVL 22

Expert Comment

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

Accepted Solution

by:
Jim Horn earned 500 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:rdm4u
ID: 39621953
You can use crosstab
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

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

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 65

Expert Comment

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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 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