Dynamic pivot with column alias
Introduction
Recently Human Resource department has initiated employee satisfaction survey in our company. As a part of this survey every employee has to give feedback on a quarterly basis (ratings are on a scale of 1 to 5, 1 is the least and 5 is highest), questions on every quarter may or may not be the same and at the end of the quarter IT department has to publish report on HR portal.
Problem Definition
Matrix report can be accomplished using pivot queries in SQL Server, but the real change was to dynamically name the column aliases, and it took us a while to come out with the solution.
Solution
For demo purpose we have simplified the table structure, we have three tables EmployeeMaster to store Employee Details, QuestionMaster which stores questions and EmployeeFeedback which stores quarterly feedback ratings.
Create & Populate EmployeeMaster
CREATE TABLE EmployeeMaster(
EmployeeCode Int Identity(1,1) Primary Key ,
EmployeeName Varchar(50) ,
JobTitle Varchar(50)
)
Insert into EmployeeMaster (EmployeeName , JobTitle)
Values('Syed Abbas' , 'Pacific Sales Manager'),
('Hazem Abolrous' , 'Quality Assurance Manager'),
('Pilar Ackerman' , 'Shipping and Receiving Supervisor'),
('François Ajenstat' , 'Database Administrator'),
('Amy Alberts' , 'European Sales Manager'),
('Sean Alexander' , 'Quality Assurance Technician'),
('Gary Altman' , 'Facilities Manager'),
('Zainal Arifin' , 'Document Control Manager'),
('Dan Bacon' , 'Application Specialist'),
('Paula Barreto de Mattos' , 'Human Resources Manager'),
('Wanida Benshoof' , 'Marketing Assistant'),
('Karen Berg' , 'Application Specialist'),
('Karen Berge' , 'Document Control Assistant'),
('Andreas Berglund' , 'Quality Assurance Technician'),
('Jo Berry' , 'Janitor')
Now I Create QuestionMaster table and insert 22 questions, out of 22 questions 10 pertaining to the year 2014, quarter 1 and 12 pertaining to the year 2014 quarter 2. SeqNo in this table represents the order in which questions appear on employee screens.
Create & Populate QuestionMaster
CREATE TABLE QuestionMaster(
QuestionCode Int Identity(1,1) Primary Key ,
Question Varchar(500) ,
YearNo INT Not Null ,
QtrNo INT Not Null ,
SeqNo Int Not Null
)
INSERT INTO QuestionMaster (Question , YearNo , QtrNo , SeqNo)Values
('Employee performance evaluations are fair and appropriate.' , 2014 , 1 , 1),
('My supervisor gives me praise and recognition when I do a good job.' , 2014 , 1 , 2),
('Teamwork is encouraged and practiced in this organization.' , 2014 , 1 , 3),
('My manager treats all his/her employees fairly.' , 2014 , 1 , 4),
('I am always treated fairly by my manager.' , 2014 , 1 , 5),
('I am paid fairly for the work I do.' , 2014 , 1 , 6),
('I have the resources I need to do my job well.' , 2014 , 1 , 9),
('My workplace is well maintained.' , 2014 , 1 , 8),
('I am very satisfied with my job.' , 2014 , 1 , 10),
('My ideas and opinions count at work.' , 2014 , 1 , 7),
('Employee performance evaluations are fair and appropriate.' , 2014 , 2 , 1),
('My supervisor gives me praise and recognition when I do a good job.' , 2014 , 2 , 2),
('Teamwork is encouraged and practiced in this organization.' , 2014 , 2 , 3),
('My manager treats all his/her employees fairly.' , 2014 , 2 , 4),
('I am always treated fairly by my manager.' , 2014 , 2 , 5),
('I am paid fairly for the work I do.' , 2014 , 2 , 6),
('I have the resources I need to do my job well.' , 2014 , 2 , 9),
('My workplace is well maintained.' , 2014 , 2 , 10),
('I am very satisfied with my job.' , 2014 , 2 , 11),
('My ideas and opinions count at work.' , 2014 , 2 , 12),
('I am encouraged to learn from my mistakes.' , 2014 , 2 , 7),
('Favouritism is not an issue in raises or promotions.' , 2014 , 2 , 8)
Finally, make some dummy employee feedback entries for 2014 quarter 1 questions (10 questions).
CREATE & POPULATE EmployeeFeedback
CREATE TABLE EmployeeFeedback(
FeedbackCode Int Identity(1,1) Primary Key ,
EmployeeCode Int Not Null ,
QuestionCode Int Not Null ,
Rating Int Not Null ,
YearNo Int Not Null ,
QtrNo Int Not Null
)
Insert Into EmployeeFeedBack (EmployeeCode , QuestionCode , Rating , YearNo , QtrNo) Values
(1 , 1 , 3 , 2014 , 1)
,(1 , 2 , 4 , 2014 , 1)
,(1 , 3 , 3 , 2014 , 1)
,(1 , 4 , 4 , 2014 , 1)
,(1 , 5 , 4 , 2014 , 1)
,(1 , 6 , 4 , 2014 , 1)
,(1 , 7 , 3 , 2014 , 1)
,(1 , 8 , 4 , 2014 , 1)
,(1 , 9 , 5 , 2014 , 1)
,(1 , 10 , 4 , 2014 , 1)
,(2 , 1 , 3 , 2014 , 1)
,(2 , 2 , 3 , 2014 , 1)
,(2 , 3 , 3 , 2014 , 1)
,(2 , 4 , 4 , 2014 , 1)
,(2 , 5 , 4 , 2014 , 1)
,(2 , 6 , 2 , 2014 , 1)
,(2 , 7 , 2 , 2014 , 1)
,(2 , 8 , 4 , 2014 , 1)
,(2 , 9 , 4 , 2014 , 1)
,(2 , 10 , 4 , 2014 , 1)
,(3 , 1 , 5 , 2014 , 1)
,(3 , 2 , 4 , 2014 , 1)
,(3 , 3 , 4 , 2014 , 1)
,(3 , 4 , 4 , 2014 , 1)
,(3 , 5 , 3 , 2014 , 1)
,(3 , 6 , 3 , 2014 , 1)
,(3 , 7 , 3 , 2014 , 1)
,(3 , 8 , 3 , 2014 , 1)
,(3 , 9 , 3 , 2014 , 1)
,(3 , 10 , 4 , 2014 , 1)
,(4 , 1 , 4 , 2014 , 1)
,(4 , 2 , 4 , 2014 , 1)
,(4 , 3 , 4 , 2014 , 1)
,(4 , 4 , 2 , 2014 , 1)
,(4 , 5 , 2 , 2014 , 1)
,(4 , 6 , 3 , 2014 , 1)
,(4 , 7 , 4 , 2014 , 1)
,(4 , 8 , 3 , 2014 , 1)
,(4 , 9 , 4 , 2014 , 1)
,(4 , 10 , 5 , 2014 , 1)
,(5 , 1 , 4 , 2014 , 1)
,(5 , 2 , 5 , 2014 , 1)
,(5 , 3 , 4 , 2014 , 1)
,(5 , 4 , 4 , 2014 , 1)
,(5 , 5 , 4 , 2014 , 1)
,(5 , 6 , 5 , 2014 , 1)
,(5 , 7 , 3 , 2014 , 1)
,(5 , 8 , 3 , 2014 , 1)
,(5 , 9 , 4 , 2014 , 1)
,(5 , 10 , 4 , 2014 , 1)
,(6 , 1 , 5 , 2014 , 1)
,(6 , 2 , 5 , 2014 , 1)
,(6 , 3 , 4 , 2014 , 1)
,(6 , 4 , 3 , 2014 , 1)
,(6 , 5 , 3 , 2014 , 1)
,(6 , 6 , 4 , 2014 , 1)
,(6 , 7 , 2 , 2014 , 1)
,(6 , 8 , 2 , 2014 , 1)
,(6 , 9 , 4 , 2014 , 1)
,(6 , 10 , 4 , 2014 , 1)
,(7 , 1 , 2 , 2014 , 1)
,(7 , 2 , 2 , 2014 , 1)
,(7 , 3 , 3 , 2014 , 1)
,(7 , 4 , 3 , 2014 , 1)
,(7 , 5 , 4 , 2014 , 1)
,(7 , 6 , 4 , 2014 , 1)
,(7 , 7 , 4 , 2014 , 1)
,(7 , 8 , 4 , 2014 , 1)
,(7 , 9 , 3 , 2014 , 1)
,(7 , 10 , 3 , 2014 , 1)
,(8 , 1 , 2 , 2014 , 1)
,(8 , 2 , 2 , 2014 , 1)
,(8 , 3 , 3 , 2014 , 1)
,(8 , 4 , 3 , 2014 , 1)
,(8 , 5 , 3 , 2014 , 1)
,(8 , 6 , 4 , 2014 , 1)
,(8 , 7 , 4 , 2014 , 1)
,(8 , 8 , 4 , 2014 , 1)
,(8 , 9 , 3 , 2014 , 1)
,(8 , 10 , 2 , 2014 , 1)
,(9 , 1 , 3 , 2014 , 1)
,(9 , 2 , 3 , 2014 , 1)
,(9 , 3 , 3 , 2014 , 1)
,(9 , 4 , 3 , 2014 , 1)
,(9 , 5 , 4 , 2014 , 1)
,(9 , 6 , 4 , 2014 , 1)
,(9 , 7 , 4 , 2014 , 1)
,(9 , 8 , 4 , 2014 , 1)
,(9 , 9 , 5 , 2014 , 1)
,(9 , 10 , 4 , 2014 , 1)
,(10 , 1 , 4 , 2014 , 1)
,(10 , 2 , 4 , 2014 , 1)
,(10 , 3 , 3 , 2014 , 1)
,(10 , 4 , 3 , 2014 , 1)
,(10 , 5 , 4 , 2014 , 1)
,(10 , 6 , 3 , 2014 , 1)
,(10 , 7 , 4 , 2014 , 1)
,(10 , 8 , 2 , 2014 , 1)
,(10 , 9 , 4 , 2014 , 1)
,(10 , 10 , 3 , 2014 , 1)
,(11 , 1 , 3 , 2014 , 1)
,(11 , 2 , 3 , 2014 , 1)
,(11 , 3 , 4 , 2014 , 1)
,(11 , 4 , 4 , 2014 , 1)
,(11 , 5 , 4 , 2014 , 1)
,(11 , 6 , 4 , 2014 , 1)
,(11 , 7 , 5 , 2014 , 1)
,(11 , 8 , 3 , 2014 , 1)
,(11 , 9 , 3 , 2014 , 1)
,(11 , 10 , 4 , 2014 , 1)
,(12 , 1 , 4 , 2014 , 1)
,(12 , 2 , 4 , 2014 , 1)
,(12 , 3 , 3 , 2014 , 1)
,(12 , 4 , 4 , 2014 , 1)
,(12 , 5 , 3 , 2014 , 1)
,(12 , 6 , 4 , 2014 , 1)
,(12 , 7 , 3 , 2014 , 1)
,(12 , 8 , 4 , 2014 , 1)
,(12 , 9 , 5 , 2014 , 1)
,(12 , 10 , 4 , 2014 , 1)
,(13 , 1 , 5 , 2014 , 1)
,(13 , 2 , 4 , 2014 , 1)
,(13 , 3 , 4 , 2014 , 1)
,(13 , 4 , 5 , 2014 , 1)
,(13 , 5 , 3 , 2014 , 1)
,(13 , 6 , 3 , 2014 , 1)
,(13 , 7 , 4 , 2014 , 1)
,(13 , 8 , 4 , 2014 , 1)
,(13 , 9 , 3 , 2014 , 1)
,(13 , 10 , 3 , 2014 , 1)
,(14 , 1 , 3 , 2014 , 1)
,(14 , 2 , 3 , 2014 , 1)
,(14 , 3 , 4 , 2014 , 1)
,(14 , 4 , 4 , 2014 , 1)
,(14 , 5 , 4 , 2014 , 1)
,(14 , 6 , 4 , 2014 , 1)
,(14 , 7 , 3 , 2014 , 1)
,(14 , 8 , 3 , 2014 , 1)
,(14 , 9 , 3 , 2014 , 1)
,(14 , 10 , 3 , 2014 , 1)
,(15 , 1 , 4 , 2014 , 1)
,(15 , 2 , 4 , 2014 , 1)
,(15 , 3 , 4 , 2014 , 1)
,(15 , 4 , 3 , 2014 , 1)
,(15 , 5 , 2 , 2014 , 1)
,(15 , 6 , 3 , 2014 , 1)
,(15 , 7 , 3 , 2014 , 1)
,(15 , 8 , 3 , 2014 , 1)
,(15 , 9 , 3 , 2014 , 1)
,(15 , 10 , 3 , 2014 , 1)
As you can see in below query result, question sequence is different than that of questionCode.
SELECT * FROM QuestionMaster Where YearNo = 2014 And QtrNo = 1 ORDER BY SeqNo
As you can see QuestionCode 10 has seq# 7, 7 has seq# 9 and 9 has seq# 10.
The report format has to be like below screenshot, Q1 represents Seq#1 followed by seq#2 etc.
**Dynamic Pivot Query **
To achieve above query result, had to create two column sets, @colalias as column alias names and @col2 as questioncode column which will be aliased as Q1, Q2, Q3 etc.
DECLARE @colalias AS NVARCHAR(MAX) , @cols2 AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) , @yearno Int = 2014 , @QtrNo Int = 2
SELECT QuestionCode , 'Q' + CAST(ROW_NUMBER() over(Order by SeqNo) as Varchar)seq INTO #Questionmaster FROM Questionmaster Where YearNo = @yearno And QtrNo = @QtrNo
SELECT @colalias = ISNULL(@colalias + ', ', '') + QUOTENAME(QuestionCode) + ' As ' + QUOTENAME(seq) FROM #Questionmaster
SET @cols2 = STUFF((SELECT ',' + QUOTENAME(QuestionCode) FROM #Questionmaster FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
SET @query = 'SELECT EmployeeName , JobTitle , ' + @colalias +
'FROM
(SELECT
em.EmployeeName , em.JobTitle , ef.questionCode, ef.Rating
FROM QuestionMaster qm
Left JOIN EmployeeFeedback ef On qm.questionCode = ef.questioncode And qm.yearno = ef.YearNo And qm.QtrNo = ef.QtrNo
Left JOIN EmployeeMaster em ON ef.EmployeeCode = em.EmployeeCode
where ef.YearNo = ' + CAST (@YearNo AS Varchar) +' and ef.QtrNo ='+ CAST(@QtrNo AS VARCHAR) +') AS SourceTable
PIVOT
(
avg(rating)
FOR QuestionCode IN (' + @cols2 + ')
) AS PivotTable Order by EmployeeName'
EXEC sp_executesql @query
Drop table #Questionmaster
Here we go with dynamic pivot with column aliased as Q1, Q2, Q3 etc. If we take feedback for quarter 2 then question set will have 12 questions with different sequence nos, and we'll get pivot result set with Q1, Q2.....Q12.
Conclusion
Have demonstrated here how we can make use of dynamic pivot functionality to generate matrix report with column alias. We have not paid much attention to table normalization and optimization in this demo, sole intention to demonstrate the pivot functionality.
References
Using PIVOT and UNPIVOT
SQL SERVER – PIVOT and UNPIVOT Table Examples