Solved

SQL Server 2K5 Piviot Query

Posted on 2008-10-08
6
184 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
  • 4
  • 2
6 Comments
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 350 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
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
Comment Utility
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
Comment Utility
Without running it ... it looks like you got it.

yes, I share your sentiments on dynamic SQL.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

17 Experts available now in Live!

Get 1:1 Help Now