SQL Server 2K5 Piviot Query
I am working with employee schedules and am trying to build a query that will return a single row of data for each employee that displays the employee information as well as what times they are scheduled for.
I have a table that looks similar to this
------------|------------|
EmpID | ShiftID |
------------|------------|
1 | 1 |
------------|------------|
1 | 2 |
------------|------------|
2 | 1 |
------------|------------|
2 | 3 |
------------|------------|
Where empID is the Key value in the employees table and the shiftID is the Key in the Shifts table.
Now, I am assuming, I will need some sort of piviot query here to get back the data that I want but have been unable to find any documents on the web that relate to doing this type of piviot, (Most all that I have read use some form of aggregate and, in my case, I have no use for aggregates)
Ultimately I am looking for the query to return:
------------|------------|
Name | Address |Shift1 | Shift2 | Shift3 |
------------|------------|
Joe | 123st | X | X | |
------------|------------|
Jane | 456st | X | | X |
------------|------------|
It is worthwhile to note that Shift and employee could be searched on so, it is quite possible, the query would return something like:
------------|------------|
Name | Address |Shift3 |
------------|------------|
Jane | 456st | X |
------------|------------|
Thanks for any help you may be able to provide.
I have a table that looks similar to this
------------|------------|
EmpID | ShiftID |
------------|------------|
1 | 1 |
------------|------------|
1 | 2 |
------------|------------|
2 | 1 |
------------|------------|
2 | 3 |
------------|------------|
Where empID is the Key value in the employees table and the shiftID is the Key in the Shifts table.
Now, I am assuming, I will need some sort of piviot query here to get back the data that I want but have been unable to find any documents on the web that relate to doing this type of piviot, (Most all that I have read use some form of aggregate and, in my case, I have no use for aggregates)
Ultimately I am looking for the query to return:
------------|------------|
Name | Address |Shift1 | Shift2 | Shift3 |
------------|------------|
Joe | 123st | X | X | |
------------|------------|
Jane | 456st | X | | X |
------------|------------|
It is worthwhile to note that Shift and employee could be searched on so, it is quite possible, the query would return something like:
------------|------------|
Name | Address |Shift3 |
------------|------------|
Jane | 456st | X |
------------|------------|
Thanks for any help you may be able to provide.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I see ...
Well, the documentation for the PIVOT keyword is here: http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx
Honestly, this is the first time I've used that keyword. I'm glad it served for the first example!
The way I have solved this problem before uses SUM() and CASE quite a bit ... but has the same limitation regarding numbers of shifts (or whatever the columns are).
Would you be interested in a dynamic SQL solution? It could get ugly ... but would probably solve the problem.
Well, the documentation for the PIVOT keyword is here: http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx
Honestly, this is the first time I've used that keyword. I'm glad it served for the first example!
The way I have solved this problem before uses SUM() and CASE quite a bit ... but has the same limitation regarding numbers of shifts (or whatever the columns are).
Would you be interested in a dynamic SQL solution? It could get ugly ... but would probably solve the problem.
If you are interested in dynamic SQL, please supply the actual table & field names. Also, will a StartShiftNumber and EndShiftNumber be sufficient criteria?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Without running it ... it looks like you got it.
yes, I share your sentiments on dynamic SQL.
yes, I share your sentiments on dynamic SQL.
ASKER
While your query works wonderfully for a static number of shifts, it does not completely solve my problem.
The problem is that, at run time, I have no idea home many shifts (total columns) will be returned. When this query is executed it ultimately will be filtered by Shifts and or Contact information. So it may execute one time looking for employees named Joe that are on schedule for shift 8 and the next time it runs it may be looking for Mary on schedule for shifts 1 - 15.
Unfortunately is does not appear as though a sub query is valid inside of the IN() statement as SSMS is throwing me back an error about it.
=\