Multiple column pivot

Question

I need help with multiple column aggregate using pivot in mssql.

Below is the temporary table for class assessments. This table contain list of class assessment which include:

assessment code
date of assessment
total item
passing percentage

create table #class_assessments (class_assessment_id int identity(1,1),
                                 class_assessment_code  varchar(10),
                                 class_assessment_date  datetime,
                                 class_assessment_total_item decimal(8,3),
                                 class_assessment_passing_item  decimal(8,2))

insert into #class_assessments values ('a1', convert(varchar(10), getdate(), 101), 10.0, 50.0)
insert into #class_assessments values ('a2', convert(varchar(10), getdate()+ 1, 101), 20.0, 50.0)
insert into #class_assessments values ('a3', convert(varchar(10), getdate()+ 2, 101), 30.0, 50.0)
insert into #class_assessments values ('a4', convert(varchar(10), getdate()+ 3, 101), 40.0, 50.0)

Below is the employee assessments. This table contains the list of employee who took the assessments:

create table #emp_assessments (emp_assessment_id int identity(1,1),
                               class_assessment_id int,
                               emp_name varchar(100),
                               assessment_score decimal(8,2),
                               assessment_comment varchar(100))
insert into #emp_assessments values(1, 'emp_name1', 5.0, 'comment1-1')
insert into #emp_assessments values(1, 'emp_name2', 5.0, 'comment1-2')

insert into #emp_assessments values(2, 'emp_name1', 5.0, 'comment2-1')
insert into #emp_assessments values(2, 'emp_name2', 5.0, 'comment2-2')

insert into #emp_assessments values(3, 'emp_name1', 5.0, 'comment3-1')
insert into #emp_assessments values(3, 'emp_name2', 5.0, 'comment3-2')

insert into #emp_assessments values(4, 'emp_name3', 5.0, 'comment4-3')
insert into #emp_assessments values(4, 'emp_name4', 5.0, 'comment4-4')

My base table is #emp_assessment_scores. This table contains the summary of all employee assessments including the percentage score and status if passed of failed.

create table #emp_assessment_scores (id int identity(1,1),
                                     emp_assessment_id int,
                                     class_assessment_id int,
                                     emp_name varchar(100),
                                     assessment_score decimal(8,2),
                                     assessment_comment varchar(100),
                                     class_assessment_code varchar(10), 
                                     class_assessment_date  datetime, 
                                     class_assessment_total_item decimal(8,2),
                                     class_assessment_passing_item decimal(8,2),
                                     score_percent decimal(8,2),
                                     score_status varchar(10))
insert into #emp_assessment_scores
select  ea.emp_assessment_id,   
        ea.class_assessment_id, 
        ea.emp_name,    
        ea.assessment_score,    
        ea.assessment_comment,  
        ca.class_assessment_code,   
        ca.class_assessment_date,   
        ca.class_assessment_total_item, 
        ca.class_assessment_passing_item,
        ea.assessment_score / ca.class_assessment_total_item * 100,
        case when ea.assessment_score / ca.class_assessment_total_item * 100 >= ca.class_assessment_passing_item then 'passed' else 'failed' end
from #emp_assessments as ea inner join #class_assessments as ca on ea.class_assessment_id = ca.class_assessment_id

below is my pivot script

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),@PivotColumnNames AS NVARCHAR(MAX)

SET @PivotColumnNames = N'';
SELECT @PivotColumnNames = @PivotColumnNames +  N', ' + QUOTENAME(class_assessment_code)
FROM( SELECT distinct(class_assessment_code) FROM #emp_assessment_scores AS p GROUP BY class_assessment_code ) AS x;

SET @DynamicPivotQuery = N'
SELECT emp_name' + @PivotColumnNames + 'FROM (
SELECT  emp_name, score_percent, class_assessment_code FROM #emp_assessment_scores) AS j 
PIVOT (max(score_percent) FOR class_assessment_code in  ('+ STUFF(@PivotColumnNames, 1, 1, '') +')) AS s ';

EXEC sp_executesql @DynamicPivotQuery

It shows this result:

+-----------+-------+-------+-------+-------+
| emp_name  |  a1   |  a2   |  a3   |  a4   |
+-----------+-------+-------+-------+-------+
| emp_name1 | 50.00 | 25.00 | 16.67 | NULL  |
| emp_name2 | 50.00 | 25.00 | 16.67 | NULL  |
| emp_name3 | NULL  | NULL  | NULL  | 12.50 |
| emp_name4 | NULL  | NULL  | NULL  | 12.50 |
+-----------+-------+-------+-------+-------+

But I wanted to have the result shown below:

+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+
| emp_name  | a1_item | a1_passing | a1_score | a1_percent | a1_comment | ai_status | a2_item | a2_passing | a2_score | a2_percent | a2_comment | a2_status | a3_item | a3_passing | a3_score | a3_percent | a3_comment | a3_status | a4_item | a4_passing | a4_score | a4_percent | a4_comment | a4_status |
+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+
| emp_name1 | 10.00   | 50.00      | 5.00     | 50.00      | comment1-1 | passed    | 20.00   | 50.00      | 5.00     | 25.00      | comment2-1 | failed    | 30.00   | 50.00      | 5.00     | 16.67      | comment3-1 | failed    | null    | null       | null     | null       | null       | null      |
| emp_name2 | 10.00   | 50.00      | 5.00     | 50.00      | comment1-2 | passed    | 20.00   | 50.00      | 5.00     | 25.00      | comment2-2 | failed    | 30.00   | 50.00      | 5.00     | 16.67      | comment3-2 | failed    | null    | null       | null     | null       | null       | null      |
| emp_name3 | null    | null       | null     | null       | null       | null      | null    | null       | null     | null       | null       | null      | null    | null       | null     | null       | null       | null      | 40.00   | 50.00      | 5.00     | 12.50      | comment4-3 | failed    |
| emp_name4 | null    | null       | null     | null       | null       | null      | null    | null       | null     | null       | null       | null      | null    | null       | null     | null       | null       | null      | 40.00   | 50.00      | 5.00     | 12.50      | comment4-3 | failed    |
+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+

What is the table #emp_assessment_scores? Maybe you could tell a little more abould your data structure to help us understand the problem. For the subquery use either DISTINCT or GROUP BY but not both. What's the use of letting SQL Server build a dynamic query string? How can the result be processed? If you're doing this for reporting purposes, I suggest to use a reporting tool (like SSRS) that has built-in matrix functionalities. — Wolfgang Kais
hi @WolfgangK, i forgot to include my base table.. i updated my question above. — Dante Salvador
I would do this using a dynamic cross tab (also known as conditional aggregation). Here is a great article on the topic. sqlservercentral.com/articles/Crosstab/65048 — Sean Lange
@DanteSalvador Thanks for including the base table. Nevertheless the result of such a query will grow 6 columns wider for every new assessment and there will be no standardized way to visualize that query result. Therefore, you will have to process the result by some hand-written procedure. If you indeed need to do so, I suggest to also do the pivoting in your application. There will be a staight forward way without any "hacking" through dynamic queries to force SQL Server to create and execte a complex query who's result is not usable by standard controls. — Wolfgang Kais

Andrea Andrea · Accepted Answer · 2018-07-13T13:32:23

You can use dynamic TSQL combined with group by to generate all the columns you need:

--declare variable that will hold the dynamic TSQL statement
declare @sql nvarchar(max)='select emp_name '

--generate the select statements for your dynamic query for each class_assessment_code
select 
   @sql = @sql +' ,sum(case when class_assessment_code='''+class_assessment_code
   +''' then class_assessment_total_item else null end) as '+class_assessment_code
   +'_item  ,sum(case when class_assessment_code='''+class_assessment_code
   +''' then class_assessment_passing_item else null end) as '+class_assessment_code
   +'_passing  ,sum(case when class_assessment_code='''+class_assessment_code
   +''' then assessment_score else null end) as '+class_assessment_code
   +'_score  ,sum(case when class_assessment_code='''+class_assessment_code
   +''' then score_percent else null end) as '+class_assessment_code
   +'_percent  ,max(case when class_assessment_code='''+class_assessment_code
   +''' then assessment_comment else null end) as '+class_assessment_code
   +'_comment  ,max(case when class_assessment_code='''+class_assessment_code
   +''' then score_status else null end) as '+class_assessment_code+'_status'
from #emp_assessment_scores
group by class_assessment_code

--add group by clause to dynamic query
set @sql = @sql +' FROM #emp_assessment_scores group by emp_name'

--execute the dynamic query
exec(@sql)

Result:

Multiple column pivot

1 Answers