Мне нужна помощь с несколькими агрегатами столбцов, используя pivot в mssql.
Ниже приведена временная таблица оценок классов. Эта таблица содержит список оценок классов, которые включают:
- оценочный код
- дата оценки
- общий товар
- процент пропущенных
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)
Ниже приведены оценки сотрудников. Эта таблица содержит список сотрудников, которые приняли оценки:
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')
Моя базовая таблица #emp_assessment_scores. Эта таблица содержит сводку всех оценок сотрудников, включая процентный балл и статус, если они прошли неудачно.
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
ниже мой скрипт
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
Он показывает этот результат:
+-----------+-------+-------+-------+-------+
| 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 |
+-----------+-------+-------+-------+-------+
Но я хотел получить результат, показанный ниже:
+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+
| 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 |
+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+
