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

目次
范例运行环境
视图样本设计
数据统计要求
 STUFF函数实现
小结

---

范例运行环境

操作体系： Windows Server 2019 DataCenter
数据库：Microsoft SQL Server 2016
视图样本设计

假设某一视图 [v_pj_rep1_lname_score] 可查询对某一被评价人的绩效指标的打分情况，并按评价人的职务举行分类， 设计如下：
序号字段名类型说明备注1projectciduniqueidentifier项目ID2wxmpciduniqueidentifier被评价人ID3count_sortidtinyint评价人职级排序号数值越小职务越高4lnamenvarchar评价人职务5rsint评价人总数6scoredecimal评价人总分所有评价人给被评价人打分的总和7score2decimal评价人平均得分所有评价人给被评价人打分的总和除以总人数的平均分 查询分析器效果数据显示如下图：

1. /****** SSMS 的 SelectTopNRows 命令的脚本  ******/
2. SELECT  [projectcid]
3.       ,[wxmpcid]
4.       ,[count_sortid]
5.       ,[lname]
6.       ,[rs]
7.       ,[score]
8.       ,[score2]
9.   FROM [v_pj_rep1_lname_score] order by projectcid,wxmpcid,count_sortid

复制代码

如图我们对项目ID、被评价人ID、评价人职务排序号举行排序，可以看到职务越高排位越靠前。
数据统计要求

假设统计视图名 [v_pj_rep1_lname_score_count] 可查询对某一被评价人的所有被评价人统计描述（如职员人数情况、每类人打分情况等），即将视图设计样本的行数据变为列举行显示， 统计表设计如下：
序号字段名类型说明备注1projectciduniqueidentifier项目ID2wxmpciduniqueidentifier被评价人ID3scoredecimal被评价人权重分1总人数的平均分*20%4score2decimal被评价人权重分2总人数的平均分*20%*30%5dnamenvarchar统计显示将行数据变为列数据，显示统计详情信息 查询分析器效果数据显示如下图：

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

 示例代码如下：

1. SET ANSI_NULLS ON
2. GO
4. SET QUOTED_IDENTIFIER ON
5. GO
7. CREATE view [dbo].[v_pj_rep1_lname_score_count] as
9. select projectcid,wxmpcid,round(sum(score2)*0.2,2) score,round(sum(score2)*0.2*0.3,2) score2,
10. 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','')+'分'   
11. from v_pj_rep1_lname_score b
12. where b.projectCid=a.projectcid and b.wxmpCid=a.wxmpCid order by count_sortid for xml path('')
13. ),1,1,'') as dname from
15.   v_pj_rep1_lname_score a group by projectcid,wxmpcid
16. GO

复制代码

关键说明见下表： 
序号关键语句说明1round(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 举行排序，数值越小的职务越往前排
3v_pj_rep1_lname_score a group by projectcid,wxmpcid对视图样本举行项目ID和被评价人ID举行分组统计
小结

SQL Server 中的 STUFF 函数是将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符；然后将第二个字符串插入到第一个字符串的开始位置。
详细语法请参照：https://learn.microsoft.com/zh-cn/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN
至此STUFF的函数使用我们就先容到这里，详细使用中我们还必要机动把握，对效果数据的细节大概要进一步举行处理，以满足我们的统计要求。并可设计导出到 WORD或EXCEL文件举行下载。


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。