1

below is the temporary table for class assessments. this table contain list of class assessment which include the assessment code, date of assessment, total item and 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 contain 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 or 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

its shows the 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

i wanted to show per assessment the total item, passing percentage, score, score percentage and the status.

please help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.