Solved

SQL Join Query Help

Posted on 2013-11-04
6
420 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.

867 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

25 Experts available now in Live!

Get 1:1 Help Now