April 11, 2008 at 1:22 pm
I have a database that contains all the responses to employee annual reviews. The employee's responses are stored (referred to as P1 for 'position 1') as well as the employee's supervisor's responses (referred to as P2 for 'position 2'). This is all stored in a very normalized fashion. However the end user insists she needs a complete dump of everyone's answers. The data has been somewhat normalized and I'm retrieving the data from these tables and pivoting the results using THIRTY TWO max(case....) statements. This is bringing the procedure to it's knees. I'm looking into the PIVOT syntax and I can get it to work for one "Assessment Area" and only the P1 responses. But I need to include the P2 responses as well. Here's what I have so far:
if object_id('tempdb..#Answers') is not null
drop table #Answers
go
create table #Answers
(UserID int,
AssessmentArea varchar(50),
AssessmentItem varchar(50),
P1Comment varchar(50),
P2Comment varchar(50))
-- Original data has been truncated to be more manageable in an example
insert into #Answers
select 110150, 'Developmental Opportunities', 'Developmental Opportunity #1',
'To attain a much better working knowledge of ASM b', 'Decrease operating expenses from current base budg'
union select 110150, 'Developmental Opportunities', 'Developmental Opportunity #2',
'To gain a more indepth understanding of ASM busine', 'Dennison Survey white space opportunities. Goal t'
union select 110150, 'Developmental Opportunities', 'Developmental Opportunity #3',
'Continue to improve on effective leadership', 'Hire one to two Fast Track candidates. Funding fo'
union select 110150, 'Future Year Objectives', 'Future Year Objective #1',
'Achieve 2008 EBITDA Target of $5,800,000', 'Exceed brokerage revenue target by 10% or $2.7 mil'
union select 110150, 'Future Year Objectives', 'Future Year Objective #2',
'Successfully acquire ABC Brokers as the launch', 'Exceed operating profit target by 10% of $570K.'
union select 110150, 'Future Year Objectives', 'Future Year Objective #3',
'Achieve top line revenue of $48,000,000 plus', 'Successfully integrate X and other US ba'
union select 110150, 'Future Year Objectives', 'Future Year Objective #4',
'Successfully attract and hire a suitable replaceme', 'Close X Brokers acquisition opportunity by A'
union select 110150, 'Strengths and Additional Skills', 'Strengths and Skills #1',
'Leadership A leader who leads from the front Ha', 'Leadership - Passion for the business - Integrity.'
union select 110150, 'Strengths and Additional Skills', 'Strengths and Skills #2',
'Personal Strenghts A willingness to push oneself ', 'Adaptable- Team Player - Experienced'
union select 110150, 'Strengths and Additional Skills', 'Strengths and Skills #3',
NULL, 'Vision - Business Knowledge- Resourceful.'
-- Pivot the 'P1' Answers to Developmental Opportunities
select UserID,
[Developmental Opportunity #1] as 'P1 Dev Opp 1',
[Developmental Opportunity #2] as 'P1 Dev Opp 2',
[Developmental Opportunity #3] as 'P1 Dev Opp 3'
from (select UserID, AssessmentArea, AssessmentItem, P1Comment
from #Answers) sq
pivot (max(P1Comment)
for AssessmentItem in
([Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3])) pt
where AssessmentArea like 'Dev%'
But now, I need to include the P2 responses. I tried adding a second pivot statement:
from (select UserID, AssessmentArea, AssessmentItem, P1Comment, P2Comment
from #Answers) sq
pivot (max(P1Comment)
for AssessmentItem in
([Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3])) as pt
pivot (max(P2Comment)
for AssessmentItem in
([Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3])) as pt2
-- but.... how do I represent this data in the select???
as well as specifying two different "max"es:
from (select UserID, AssessmentArea, AssessmentItem, P1Comment, P2Comment
from #Answers) sq
pivot (max(P1Comment), max(P2Comment)
for AssessmentItem in
([Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3])) as pt
Both crashed and burned. Add to that the fact that I need to do this for all "Assessment Areas" the result being (with data truncated and only showing Developmental Opportunities for brevity - need to also show "Strenghts and Additional Skills", etc.):
UserID P1 Dev Opp 1 P2 Dev Opp 1 P1 Dev Opp 2 P2 Dev Opp 2 P1 Dev Opp 3 P2 Dev Opp 3
------- ------------- ------------- ------------- ------------- ------------- -------------
110150 To attain a Decrease ope To gain a mo Dennison Sur Continue to Hire one or t
Is there ANY way to do this? Or am I going to have to query each area and responder's comment separately and then join it all back together??
Thank you -
Lisa
P.S. Sorry last code block is displaying so wide. Can't get it any narrower.
April 11, 2008 at 1:30 pm
Is there a technical reason to do the pivot in the database?
Would it be possible to move the pivot to a front-end application?
The reason I ask is that Excel, Report Services, Access, and any number of other presentation applications are much better at pivoting data than SQL is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 11, 2008 at 1:35 pm
That is a good suggestion. This data is being consumed by a .NET web application. I will look into that.
Thanks -
Lisa
May 1, 2008 at 5:33 pm
I wanted to provide an update to this in the event someone researches PIVOT. I tried having the front end perform the pivot, however each employee's data contained 20 rows which amounted to around 150,000 rows being returned to the front end. For this particular case, that wouldn't work. So I went back to the drawing board with the PIVOT syntax. Although I had lots of columns, I only needed to pivot the data on TWO things - P1 responses and P2 responses. So I created a pivot for each and did a UNION. I added a third select in the UNION to include the common data that didn't need to be pivoted. I could have probably included that data in the two pivots. Perhaps I will update that when time permits 😉 ! What I found was that using the PIVOT in a UNION statement turned out to be the ticket! I still needed to include MAX logic because doing a UNION on the pivots resulted in two records per person. From the front end, this horrendous report was taking anywhere from 10-30 minutes depending on time of day the report was run. Utilizing the PIVOT logic dropped the time to less than 2 minutes during peak time and less than 1 minute other times! The full code is below:
select [Assoc Employee ID], Associate,
[Supv Employee ID], Supervisor,
[DVP Employee ID], DVP,
JobCode, JobTitle, FormName,
Division, Subdivision, DeptNo, [Department Name],
max([P1 Prior Year Objective #1]) as 'P1 Prior Year Objective #1',
max([P1 Prior Year Objective #2]) as 'P1 Prior Year Objective #2',
max([P1 Prior Year Objective #3]) as 'P1 Prior Year Objective #3',
max([P1 Prior Year Objective #4]) as 'P1 Prior Year Objective #4',
max([P2 Prior Year Objective #1]) as 'P2 Prior Year Objective #1',
max([P2 Prior Year Objective #2]) as 'P2 Prior Year Objective #2',
max([P2 Prior Year Objective #3]) as 'P2 Prior Year Objective #3',
max([P2 Prior Year Objective #4]) as 'P2 Prior Year Objective #4',
max([P1 Strengths and Skills #1]) as 'P1 Strengths and Skills #1',
max([P1 Strengths and Skills #2]) as 'P1 Strengths and Skills #2',
max([P1 Strengths and Skills #3]) as 'P1 Strengths and Skills #3',
max([P2 Strengths and Skills #1]) as 'P2 Strengths and Skills #1',
max([P2 Strengths and Skills #2]) as 'P2 Strengths and Skills #2',
max([P2 Strengths and Skills #3]) as 'P2 Strengths and Skills #3',
max([P1 Developmental Opportunity #1]) as 'P1 Developmental Opportunity #1',
max([P1 Developmental Opportunity #2]) as 'P1 Developmental Opportunity #2',
max([P1 Developmental Opportunity #3]) as 'P1 Developmental Opportunity #3',
max([P2 Developmental Opportunity #1]) as 'P2 Developmental Opportunity #1',
max([P2 Developmental Opportunity #2]) as 'P2 Developmental Opportunity #2',
max([P2 Developmental Opportunity #3]) as 'P2 Developmental Opportunity #3',
max([P1 Future Year Objective #1]) as 'P1 Future Year Objective #1',
max([P1 Future Year Objective #2]) as 'P1 Future Year Objective #2',
max([P1 Future Year Objective #3]) as 'P1 Future Year Objective #3',
max([P1 Future Year Objective #4]) as 'P1 Future Year Objective #4',
max([P2 Future Year Objective #1]) as 'P2 Future Year Objective #1',
max([P2 Future Year Objective #2]) as 'P2 Future Year Objective #2',
max([P2 Future Year Objective #3]) as 'P2 Future Year Objective #3',
max([P2 Future Year Objective #4]) as 'P2 Future Year Objective #4',
max([Mobility Considerations]) as 'Mobility Considerations',
max([Location Preference]) as 'Location Preference',
max([Dept Transfer Interest]) as 'Dept Transfer Interest',
max([P1 Comment]) as 'P1 Comment',
max([P2 Comment]) as 'P2 Comment',
max([Overall Score]) as 'Overall Score',
max([Rating]) as 'Rating',
max([Rating Level]) as 'Rating Level'
from
(
-- Pivot P1 Answers
select P1EmployeeID as 'Assoc Employee ID', Associate,
P2EmployeeID as 'Supv Employee ID', Supervisor,
HREmployeeID as 'HR Employee ID',
DVPEmployeeID as 'DVP Employee ID', DVP,
JobCode, JobTitle, FormName,
Division, Subdivision, DeptNo, [Department Name],
-- Prior Year Obj from P1
[Prior Year Objective #1] as 'P1 Prior Year Objective #1',
[Prior Year Objective #2] as 'P1 Prior Year Objective #2',
[Prior Year Objective #3] as 'P1 Prior Year Objective #3',
[Prior Year Objective #4] as 'P1 Prior Year Objective #4',
-- Prior Year Obj from P2
'' as 'P2 Prior Year Objective #1',
'' as 'P2 Prior Year Objective #2',
'' as 'P2 Prior Year Objective #3',
'' as 'P2 Prior Year Objective #4',
-- Strengths from P1
[Strengths and Skills #1] as 'P1 Strengths and Skills #1',
[Strengths and Skills #2] as 'P1 Strengths and Skills #2',
[Strengths and Skills #3] as 'P1 Strengths and Skills #3',
-- Strengths from P2
'' as 'P2 Strengths and Skills #1',
'' as 'P2 Strengths and Skills #2',
'' as 'P2 Strengths and Skills #3',
-- Dev Opp from P1
[Developmental Opportunity #1] as 'P1 Developmental Opportunity #1',
[Developmental Opportunity #2] as 'P1 Developmental Opportunity #2',
[Developmental Opportunity #3] as 'P1 Developmental Opportunity #3',
-- Dev Opp from P2
'' as 'P2 Developmental Opportunity #1',
'' as 'P2 Developmental Opportunity #2',
'' as 'P2 Developmental Opportunity #3',
-- Future Year Obj from P1
[Future Year Objective #1] as 'P1 Future Year Objective #1',
[Future Year Objective #2] as 'P1 Future Year Objective #2',
[Future Year Objective #3] as 'P1 Future Year Objective #3',
[Future Year Objective #4] as 'P1 Future Year Objective #4',
-- Future Year Obj from P2
'' as 'P2 Future Year Objective #1',
'' as 'P2 Future Year Objective #2',
'' as 'P2 Future Year Objective #3',
'' as 'P2 Future Year Objective #4',
-- Career Goals
'' as 'Mobility Considerations',
'' as 'Location Preference',
'' as 'Dept Transfer Interest',
-- Overall Comments
'' as 'P1 Comment',
'' as 'P2 Comment',
-- Score
'' as 'Overall Score',
'' as 'Rating',
'' as 'Rating Level'
from (select distinct r1.P1EmployeeID, r1.P1LastName + ', ' + r1.P1FirstName as 'Associate',
r1.P2EmployeeID, r1.P2LastName + ', ' + r1.P2FirstName as 'Supervisor',
r1.HREmployeeID, r1.HRLastName + ', ' + r1.HRFirstName as 'HR Rep',
r1.DVPEmployeeID, r1.DVPLastName + ', ' + r1.DVPFirstName as 'DVP',
r1.JobCode, r1.JobTitle, r1.FormName,
r1.DivisionName as 'Division',
r1.SubdivisionName as 'Subdivision',
r1.DepartmentNumber as 'DeptNo',
r1.DepartmentName as 'Department Name',
IsNull(r2.AssessmentArea, '') as 'AssessmentArea',
IsNull(r2.AssessmentItem, '') as 'AssessmentItem',
IsNull(r2.P1Comment, '') as 'P1Comment'
from CalibrationData r1
left join CalibrationText r2 on r1.P1UserAssessmentInfoID = r2.P1UserAssessmentInfoID
where AssessmentPeriodID = 5
and r2.AssessmentArea in ('Prior Year Objectives', 'Strengths and Additional Skills',
'Developmental Opportunities', 'Future Year Objectives')
) sq
pivot (max(P1Comment)
for AssessmentItem in ([Prior Year Objective #1], [Prior Year Objective #2],
[Prior Year Objective #3], [Prior Year Objective #4],
[Strengths and Skills #1], [Strengths and Skills #2], [Strengths and Skills #3],
[Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3],
[Future Year Objective #1], [Future Year Objective #2],
[Future Year Objective #3], [Future Year Objective #4])
) pvt -- supposed to be ") pvt" instead of wink!
UNION
-- Pivot P2 Answers
select P1EmployeeID, Associate,
P2EmployeeID, Supervisor,
HREmployeeID,
DVPEmployeeID, DVP,
JobCode, JobTitle, FormName,
DivisionName, SubdivisionName, DepartmentNumber, DepartmentName,
-- Prior Year Obj from P1
'', '', '', '',
-- Prior Year Obj from P2
[Prior Year Objective #1],
[Prior Year Objective #2],
[Prior Year Objective #3],
[Prior Year Objective #4],
-- Strengths from P1
'', '', '',
-- Strengths from P2
[Strengths and Skills #1],
[Strengths and Skills #2],
[Strengths and Skills #3],
-- Dev Opp from P1
'', '', '',
-- Dev Opp from P2
[Developmental Opportunity #1],
[Developmental Opportunity #2],
[Developmental Opportunity #3],
-- Future Year Obj from P1
'', '', '', '',
-- Future Year Obj from P2
[Future Year Objective #1],
[Future Year Objective #2],
[Future Year Objective #3],
[Future Year Objective #4],
-- Career Goals
'', '', '',
-- Overall Comments
'', '',
-- Score
'', '', ''
from (select distinct r1.P1EmployeeID, r1.P1LastName + ', ' + r1.P1FirstName as 'Associate',
r1.P2EmployeeID, r1.P2LastName + ', ' + r1.P2FirstName as 'Supervisor',
r1.HREmployeeID, r1.HRLastName + ', ' + r1.HRFirstName as 'HR Rep',
r1.DVPEmployeeID, r1.DVPLastName + ', ' + r1.DVPFirstName as 'DVP',
r1.JobCode, r1.JobTitle, r1.FormName,
r1.DivisionName, r1.SubdivisionName, r1.DepartmentNumber, r1.DepartmentName,
IsNull(r2.AssessmentArea, '') as 'AssessmentArea',
IsNull(r2.AssessmentItem, '') as 'AssessmentItem',
IsNull(r2.P2Comment, '') as 'P2Comment'
from CalibrationData r1
left join CalibrationText r2 on r1.P1UserAssessmentInfoID = r2.P1UserAssessmentInfoID
where AssessmentPeriodID = 5
and r2.AssessmentArea in ('Prior Year Objectives', 'Strengths and Additional Skills',
'Developmental Opportunities', 'Future Year Objectives')
) sq
pivot (max(P2Comment)
for AssessmentItem in ([Prior Year Objective #1], [Prior Year Objective #2],
[Prior Year Objective #3], [Prior Year Objective #4],
[Strengths and Skills #1], [Strengths and Skills #2], [Strengths and Skills #3],
[Developmental Opportunity #1], [Developmental Opportunity #2], [Developmental Opportunity #3],
[Future Year Objective #1], [Future Year Objective #2],
[Future Year Objective #3], [Future Year Objective #4])
) pvt -- supposed to be ") pvt" instead of wink!
UNION
-- Career, Overall Comments, Overall Score
select distinct r1.P1EmployeeID, r1.P1LastName + ', ' + r1.P1FirstName as 'Associate',
r1.P2EmployeeID, r1.P2LastName + ', ' + r1.P2FirstName as 'Supervisor',
r1.HREmployeeID, r1.HRLastName + ', ' + r1.HRFirstName as 'HR Rep',
r1.DVPEmployeeID, r1.DVPLastName + ', ' + r1.DVPFirstName as 'DVP',
r1.JobCode, r1.JobTitle, r1.FormName,
r1.DivisionName, r1.SubdivisionName, r1.DepartmentNumber, r1.DepartmentName,
-- Prior Year Obj from P1
'', '', '', '',
-- Prior Year Obj from P2
'', '', '', '',
-- Strengths from P1
'', '', '',
-- Strengths from P2
'', '', '',
-- Dev Opp from P1
'', '', '',
-- Dev Opp from P2
'', '', '',
-- Future Year Obj from P1
'', '', '', '',
-- Future Year Obj from P2
'', '', '', '',
-- Career Goals
IsNull(cc.MobilityConsiderations, '') as 'Mobility Considerations',
IsNull(cc.LocationPreference, '') as 'Location Preference',
IsNull(cc.DepartmentTransferInterest, '') as 'Dept Transfer Interest',
-- Overall Comments
IsNull(c1.Comment, '') as 'P1 Comment',
IsNull(c2.Comment, '') as 'P2 Comment',
-- Score
IsNull(convert(varchar, r1.OverallScore), '') as 'Overall Score',
IsNull(convert(varchar, r1.Rating), '') as 'Rating',
r1.RatingName as 'Rating Level'
from CalibrationData r1
left join CalibrationCareer cc on r1.P1UserAssessmentInfoID = cc.P1UserAssessmentInfoID
left join CalibrationComments c1 on r1.P1UserAssessmentInfoID = c1.P1UserAssessmentInfoID and c1.ResponderPosition = 1
left join CalibrationComments c2 on r1.P1UserAssessmentInfoID = c2.P1UserAssessmentInfoID and c2.ResponderPosition = 2
where r1.AssessmentPeriodID = 5
) u
group by [Assoc Employee ID], Associate,
[Supv Employee ID], Supervisor,
[DVP Employee ID], DVP,
JobCode, JobTitle, FormName,
Division, Subdivision, DeptNo, [Department Name]
order by Associate
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply