multiple column pivot in mssql
-
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.
-
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) -
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 -
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply