Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

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!
0
Norm-al
Asked:
Norm-al
  • 8
  • 5
1 Solution
 
Brian CroweDatabase 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
 
Norm-alNetwork EngineerAuthor 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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
Norm-alNetwork EngineerAuthor 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
 
Norm-alNetwork EngineerAuthor Commented:
2005.
0
 
Brian CroweDatabase AdministratorCommented:
What is your compatibility level?

SELECT name, compatibility_level FROM sys.databases
0
 
Norm-alNetwork EngineerAuthor Commented:
90 on all databases
0
 
Norm-alNetwork EngineerAuthor 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
 
Norm-alNetwork EngineerAuthor 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
 
Norm-alNetwork EngineerAuthor 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
 
Norm-alNetwork EngineerAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now