sql-server - 多列数据透视表

Question

我需要有关在 mssql 中使用 pivot 的多列聚合的帮助。

下面是类(class)评估的临时表格。此表包含类(class)评估列表，其中包括:

• 评估代码
• 评估日期
• 总项
• 及格率

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    |
+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+---------+------------+----------+------------+------------+-----------+

Answer 1

您可以使用动态 TSQL 结合 group by 来生成您需要的所有列:

--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)

结果: