多列数据透视表

Question

我需要帮助使用mssql中的pivot进行多列聚合。

以下是课程评估的临时表。该表包含以下课程评估列表：

• 评估代码
• 评估日期
• 总项目数
• 及格百分比

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

- Dante Salvador

---

表#emp_assessment_scores是什么？也许您可以多说一些关于数据结构的内容，以帮助我们理解问题。对于子查询，请使用DISTINCT或GROUP BY中的一个，而不是两者都使用。让SQL Server构建动态查询字符串有什么用处？结果如何处理？如果您正在为报告目的进行此操作，我建议使用具有内置矩阵功能的报告工具（如SSRS）。 - Wolfgang Kais

嗨@WolfgangK，我忘记包含我的基础表了...我已经更新了我的问题。 - Dante Salvador

我会使用动态交叉表（也称为条件聚合）来完成此任务。这里有一篇很棒的关于这个主题的文章。http://www.sqlservercentral.com/articles/Crosstab/65048/ - Sean Lange

@DanteSalvador 感谢您包含基础表。然而，这样的查询结果每次进行新评估时都会增加6列，并且没有标准化的方法来可视化该查询结果。因此，您将不得不通过一些手写程序来处理结果。如果确实需要这样做，我建议您在应用程序中也进行数据透视。有一种简单直接的方法，无需通过动态查询“黑客”来强制SQL Server创建和执行一个复杂的查询，其结果不能被标准控件使用。 - Wolfgang Kais

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)

结果：

这是一个显示图片的HTML代码。

- Andrea

---

谢谢@andrea，我以为这个问题的唯一解决方案是Pivot..非常感谢。 - Dante Salvador