Solved

SQL Server 2K5 Piviot Query

Posted on 2008-10-08
6
199 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:itbox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 350 total points
ID: 22671637
OK, I think this should get it ...


Select Name, Address, [1] as Shift1, [2] as Shift2, [3] as Shift3
From
(Select e.Name, e.Address, e.ID, S.ShiftID
from Employee e Inner Join EmployeeSched S on e.ID = S.employeeid) ES
PIVOT
(
count(ES.ID)
For ShiftID IN ( [1], [2], [3])
) as pvt
Order by ES.Name

Open in new window

0
 

Author Comment

by:itbox
ID: 22671760
Hi Daniel, thanks for your response.

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.

=\
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22671859
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.
0
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!

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22671974
If you are interested in dynamic SQL, please supply the actual table & field names.  Also, will a StartShiftNumber and EndShiftNumber be sufficient criteria?
0
 

Accepted Solution

by:
itbox earned 0 total points
ID: 22672660
Hi Daniel,
   I read your earlier response and that is what I was afraid of.  Dynamic SQL is the devil, no really ;]  Anyway I wasn't paying attention to my email after I read your response and generated the dynamic sql method. (It obviously needs refactored a little but it works ;]) Code follows.

Thanks for all your help.
DECLARE @table table (id int identity(1,1), ShiftID uniqueidentifier)
INSERT INTO @Table(ShiftID)
SELECT DISTINCT ShiftID
FROM dbo.Contacts 
INNER JOIN dbo.ContactToSchedule on Contacts.ContactID = ContactToSchedule.ContactID
 
--SELECT * from @table
 
DECLARE @iTableRowCount int
DECLARE @iWhileCount int
DECLARE @sColumnList nvarchar(max)
DECLARE @sIDList nvarchar(max)
SET @sColumnList = ''
SET @sIDList = ''
 
Set @iTableRowCount = (SELECT count(id) from @table)
Set @iWhileCount = 1
 
WHILE(@iWhileCount <= (@iTableRowCount))
	Begin
		SET @sIDList = @sIDList +  '[' + Convert(nvarchar(255), (SELECT ShiftID from @table where ID = @iWhileCount)) + '] '
		SET @sColumnList = @sColumnList + '[' + Convert(nvarchar(255), (SELECT ShiftID from @table where ID = @iWhileCount)) + '] as '
		SET @sColumnList = @sColumnList + '''' + (
											SELECT 
												Convert(varchar(101), d.Date, 101) + '_' + a.shorttitle + '_' + c.shiftshorttitle
											FROM
												Shifts c
											LEFT JOIN dbo.Days	d			on d.ID = c.DayGuid
											LEFT JOIN dbo.Activity a        on a.ID = c.ShiftID
											WHERE c.ID = (SELECT ShiftID from @table where ID = @iWhileCount)
										  ) + ''''
		--PRINT @sColumnList
		If(@iWhileCount < @iTableRowCount)
			Begin
				SET @sColumnList = @sColumnList + ', '
				SET @sIDList = @sIDList + ', '
			End
		SET @iWhileCount = @iWhileCount + 1
		
	End
--PRINT @sColumnList
--SELECT @sColumnList
 
DECLARE @sql varchar(max)
SET @sql = 'Select Firstname, Lastname, HomeAddress, ' + @sColumnList + '
		From (Select e.FirstName, e.LastName, e.HomeAddress, e.ID, S.ShiftID
        from dbo.Contacts e Inner Join dbo.ContactToSchedule S on e.ContactID = S.ContactID) ES
        PIVOT
		(
		count(ES.ShiftID)
		For ShiftID IN (' + @sIDList + ')
		) as pvt'
--SELECT @sql
EXEC(@sql)

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22672687
Without running it ... it looks like you got it.

yes, I share your sentiments on dynamic SQL.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 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