MS SQL Server STUFF 函数实战统计记录行转为列显示

范例运行环境

视图样本设计

数据统计要求

STUFF函数实现

小结

范例运行环境

操作系统： Windows Server 2019 DataCenter

数据库：Microsoft SQL Server 2016

视图样本设计

假设某一视图 [v_pj_rep1_lname_score] 可查询对某一被评价人的绩效指标的打分情况，并按评价人的职务进行分类，设计如下：

序号	字段名	类型	说明	备注
1	projectcid	uniqueidentifier	项目ID
2	wxmpcid	uniqueidentifier	被评价人ID
3	count_sortid	tinyint	评价人职级排序号	数值越小职务越高
4	lname	nvarchar	评价人职务
5	rs	int	评价人总数
6	score	decimal	评价人总分	所有评价人给被评价人打分的总和
7	score2	decimal	评价人平均得分	所有评价人给被评价人打分的总和除以总人数的平均分

查询分析器结果数据显示如下图：

/****** SSMS 的 SelectTopNRows 命令的脚本  ******/
SELECT  [projectcid],[wxmpcid],[count_sortid],[lname],[rs],[score],[score2]FROM [v_pj_rep1_lname_score] order by projectcid,wxmpcid,count_sortid

如图我们对项目ID、被评价人ID、评价人职务排序号进行排序，可以看到职务越高排位越靠前。

数据统计要求

假设统计视图名 [v_pj_rep1_lname_score_count] 可查询对某一被评价人的所有被评价人统计描述（如人员人数情况、每类人打分情况等），即将视图设计样本的行数据变为列进行显示，统计表设计如下：

序号	字段名	类型	说明	备注
1	projectcid	uniqueidentifier	项目ID
2	wxmpcid	uniqueidentifier	被评价人ID
3	score	decimal	被评价人权重分1	总人数的平均分*20%
4	score2	decimal	被评价人权重分2	总人数的平均分20%30%
5	dname	nvarchar	统计显示	将行数据变为列数据，显示统计详情信息

查询分析器结果数据显示如下图：

如图第一行数据 dname 列返回 “董事长88.21分,总经理100.00分,分协管领导92.23分,其他领导91.79分,部门职工(2人,总分187.65分)93.83分” ，该列会显示各职务打分的人数，总分及平均分情况，从统计结果来看，更加直观。

STUFF函数实现

示例代码如下：

SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE view [dbo].[v_pj_rep1_lname_score_count] as select projectcid,wxmpcid,round(sum(score2)*0.2,2) score,round(sum(score2)*0.2*0.3,2) score2,
STUFF(( select ','+b.lname+case when rs>1 then '('+cast(rs as varchar(20))+'人,总分'++REPLACE(cast(round(score,2) as nvarchar(20)),'0000','')+'分'+')' else '' end+REPLACE(cast(round(score2,2) as nvarchar(20)),'0000','')+'分'   
from v_pj_rep1_lname_score b 
where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('')
),1,1,'') as dname from v_pj_rep1_lname_score a group by projectcid,wxmpcid
GO

关键说明见下表：

序号	关键语句	说明
1	round(sum(score2)0.2,2) score, round(sum(score2)0.2*0.3,2) score2,	取权重值，并使用ROUND函数取后两位小数
2	STUFF(( select ','+b.lname+case when rs>1 then '('+cast(rs as varchar(20))+'人,总分'++REPLACE(cast(round(score,2) as nvarchar(20)),'0000','')+'分'+')' else '' end+REPLACE(cast(round(score2,2) as nvarchar(20)),'0000','')+'分' from v_pj_rep1_lname_score b where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('') ),1,1,'') as dname	使用 STUFF 函数配合 SQL 语句 FOR XML PATH 来实现行转列。 SQL语句中通过 CASE 来判断人数，大于1则显示人数和总分，否则直接显示分值，并在前面加上职务 lname 字段，并以 count_sortid 进行排序，数值越小的职务越往前排
3	v_pj_rep1_lname_score a group by projectcid,wxmpcid	对视图样本进行项目ID和被评价人ID进行分组统计

序号

关键语句

说明

round(sum(score2)*0.2,2) score, round(sum(score2)*0.2*0.3,2) score2,

取权重值，并使用ROUND函数取后两位小数

STUFF((

select ','+b.lname+case when rs>1 then '('+cast(rs as varchar(20))+'人,总分'++REPLACE(cast(round(score,2) as nvarchar(20)),'0000','')+'分'+')' else '' end+REPLACE(cast(round(score2,2) as nvarchar(20)),'0000','')+'分'
from v_pj_rep1_lname_score b
where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('')
),1,1,'') as dname

使用 STUFF 函数配合 SQL 语句 FOR XML PATH 来实现行转列。

SQL语句中通过 CASE 来判断人数，大于1则显示人数和总分，否则直接显示分值，并在前面加上职务 lname 字段，并以 count_sortid 进行排序，数值越小的职务越往前排

v_pj_rep1_lname_score a group by projectcid,wxmpcid

对视图样本进行项目ID和被评价人ID进行分组统计