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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You would need to add any new tasks to the select list and the IN portion of the UNPIVOT.
ASKER
I just attempted this and it said UNPIVOT task is unsupported.
What version of SQL Server are you running?
ASKER
2005.
What is your compatibility level?
SELECT name, compatibility_level FROM sys.databases
SELECT name, compatibility_level FROM sys.databases
ASKER
90 on all databases
ASKER
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.
ASKER
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?
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?
ASKER
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?
ASKER
Great feedback, helped big time. Thanks!!!
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
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
ASKER