Solved

Complex SQL View Help Needed

Posted on 2013-12-05
13
170 Views
Last Modified: 2013-12-16
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
Comment
Question by:Bianca
  • 8
  • 5
13 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39698732
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
 

Author Comment

by:Bianca
ID: 39698781
So I would need this per task correct?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39698862
You would need to add any new tasks to the select list and the IN portion of the UNPIVOT.
0
 

Author Comment

by:Bianca
ID: 39722050
I just attempted this and it said UNPIVOT task is unsupported.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39722164
What version of SQL Server are you running?
0
 

Author Comment

by:Bianca
ID: 39722169
2005.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39722173
What is your compatibility level?

SELECT name, compatibility_level FROM sys.databases
0
 

Author Comment

by:Bianca
ID: 39722176
90 on all databases
0
 

Author Comment

by:Bianca
ID: 39722180
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
 

Author Comment

by:Bianca
ID: 39722185
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
 

Author Comment

by:Bianca
ID: 39722193
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
 

Author Closing Comment

by:Bianca
ID: 39722662
Great feedback, helped big time. Thanks!!!
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39722665
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

932 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

11 Experts available now in Live!

Get 1:1 Help Now