Complex SQL View Help Needed

My base tables are set up by department, columns are tasks for that department and the data rows contain employee name, training hours and training date for each task.

I need a view that combines these department tables (that contain different number of tasks per dept) grouped by task and/or employee.  Resulting DataSet needed is shown at the below these 2 base table examples:

BASE TABLE 1 - Engineering (3 tasks are DoorOrders, JobPrep & JobEng)
ID     DoorOrders     JobPrep     JobEng    Employee      Date  
01           5                  null           3              Bill          1/1/13    
02          null               8              null           Bob         1/1/13  
03          8                   null          null           Bill          1/2/13  

BASE TABLE 2 - Milling (2 tasks are SheetGoods & Nailers)
ID     SheetGoods     Nailers    Employee      Date  
01           3                  5                 Jen             1/1/13    
02          null               8              Mary           1/1/13  
03          8                   null               Jill               1/2/13  

RESULTING VIEW - (Combining these 2 departments)
TaskName    Employee    Hours      Date
DoorOrders       Bill             5         1/1/13
JobEng               Bill             3         1/1/13
JobPrep             Bob            8          1/1/13
DoorOrders      Bill             8          1/2/13
SheetGoods      Jen             3           1/1/13
Nailers             Jen              5           1/1/13
Nailers             Mary           8            1/1/13
SheetGoods     Jill               8            1/2/13

As you can see, I need to take each 'cell value' per employee per date per task and split it out in a vertical table setup.

I need a view, nothing editable, but something that I can expand as we add more departments and/or tasks per department.

Thanks!
BiancaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
SELECT TaskName, Employee, [Hours], [Date]
FROM (
      SELECT ID, DoorOrders, JobPrep, JobEng, Employee, [Date]
      FROM Engineering
      ) AS E
      UNPIVOT
      (
            [Hours] FOR TaskName IN (DoorOrders, JobPrep, JobEng)
      ) AS U

UNION ALL

SELECT TaskName, Employee, [Hours], [Date]
FROM (
      SELECT ID, SheetGoods, Nailers, Employee, [Date]
      FROM Milling
      ) AS M
      UNPIVOT
      (
            [Hours] FOR TaskName IN (SheetGoods, Nailers)
      ) AS U
0
 
BiancaAuthor Commented:
So I would need this per task correct?
0
 
Brian CroweDatabase AdministratorCommented:
You would need to add any new tasks to the select list and the IN portion of the UNPIVOT.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
BiancaAuthor Commented:
I just attempted this and it said UNPIVOT task is unsupported.
0
 
Brian CroweDatabase AdministratorCommented:
What version of SQL Server are you running?
0
 
BiancaAuthor Commented:
2005.
0
 
Brian CroweDatabase AdministratorCommented:
What is your compatibility level?

SELECT name, compatibility_level FROM sys.databases
0
 
BiancaAuthor Commented:
90 on all databases
0
 
BiancaAuthor Commented:
I ran the query directly from SQL Server Studio (was previously on Express which was giving me that error) and it doesn't say it cannot run it but it just shuts down the whole SQL Studio window.
0
 
BiancaAuthor Commented:
I apologize, I had been using the wrong field names. This one ran fine:
SELECT TaskName, Trainee, [Hours], TrainingDate
FROM (
      SELECT ID, DoorOrders, JobPrep, JobEng, Trainee, TrainingDate
      FROM Training_Eng
      ) AS E
      UNPIVOT
      (
            [Hours] FOR TaskName IN (DoorOrders, JobPrep, JobEng)
      ) AS U

Now to do the union for each task correct?
0
 
BiancaAuthor Commented:
I was creating it under 'View' my mistake, I have never run this type before. So I run it as a new query and how does the result set get saved into a table I can access by my client application?
0
 
BiancaAuthor Commented:
Great feedback, helped big time. Thanks!!!
0
 
Brian CroweDatabase AdministratorCommented:
If it's something you're going to use regularly...

CREATE VIEW <View Name>
AS

SELECT TaskName, Employee, [Hours], [Date]
FROM (
      SELECT ID, DoorOrders, JobPrep, JobEng, Employee, [Date]
      FROM Engineering
      ) AS E
      UNPIVOT
      (
            [Hours] FOR TaskName IN (DoorOrders, JobPrep, JobEng)
      ) AS U

UNION ALL

SELECT TaskName, Employee, [Hours], [Date]
FROM (
      SELECT ID, SheetGoods, Nailers, Employee, [Date]
      FROM Milling
      ) AS M
      UNPIVOT
      (
            [Hours] FOR TaskName IN (SheetGoods, Nailers)
      ) AS U
0
All Courses

From novice to tech pro — start learning today.