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
 

Download

Download this T-SQL script from the gallery.