July 12, 2018 at 11:54 am
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_idbelow 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 @DynamicPivotQueryits 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.50but 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-4 failedplease help.
July 13, 2018 at 10:08 am
I'm thinking that it may be plausible to do this more easily in SSRS than in T-SQL, and I'm going to try it, but in the meantime, I'm posting the SQL code that would deliver the necessary data set to such a report, and maybe someone else can take it the rest of the way in T-SQL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 13, 2018 at 10:36 am
Here's a sample on how to do it, but it needs to be done using cross tabs instead of PIVOT to avoid performance issues.
SELECT eas.emp_name,
SUM(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.class_assessment_total_item END) AS a1_item,
SUM(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.class_assessment_passing_item END) AS a1_passing,
SUM(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.assessment_score END) AS a1_score,
SUM(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.score_percent END) AS a1_percent,
MAX(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.assessment_comment END) AS a1_comment,
MAX(CASE WHEN eas.class_assessment_code = 'a1' THEN eas.score_status END) AS a1_status,
SUM(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.class_assessment_total_item END) AS a2_item,
SUM(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.class_assessment_passing_item END) AS a2_passing,
SUM(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.assessment_score END) AS a2_score,
SUM(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.score_percent END) AS a2_percent,
MAX(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.assessment_comment END) AS a2_comment,
MAX(CASE WHEN eas.class_assessment_code = 'a2' THEN eas.score_status END) AS a2_status
FROM #emp_assessment_scores AS eas
GROUP BY eas.emp_name;
More information in here:
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - SQLServerCentral
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - SQLServerCentral
July 13, 2018 at 11:19 am
Here's the RDL for the matrix-based cross tab report based on my query from my previous post. Luis also has the right idea. T-SQL can do it as a CROSS TAB methodology, but it's not going to be entirely dynamic, so new values would require code changes, whereas the SSRS report would not.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply