Solved

Complex SQL View Help Needed

Posted on 2013-12-05
13
172 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

813 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

10 Experts available now in Live!

Get 1:1 Help Now