Solved

Complex SQL View Help Needed

Posted on 2013-12-05
13
169 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

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.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

12 Experts available now in Live!

Get 1:1 Help Now