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

这篇具有很好参考价值的文章主要介绍了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 评价人平均得分 所有评价人给被评价人打分的总和除以总人数的平均分

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

ms sql stuff,数据库,sql,数据库开发,sqlserver,大数据

/****** 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 统计显示 将行数据变为列数据,显示统计详情信息

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

ms sql stuff,数据库,sql,数据库开发,sqlserver,大数据

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

 STUFF函数实现

 示例代码如下:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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进行分组统计

小结

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文件进行下载。文章来源地址https://www.toymoban.com/news/detail-854175.html

到了这里,关于MS SQL Server STUFF 函数实战 统计记录行转为列显示的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Delphi 中操作MS SQL Server 返回刚插入的自增ID列值

    在开发数据库的应用的时候,经常需要写入日志,比如某个日志需要记录用户打开程序的时间和用户关闭程序的时间,显然这是两个动作,因为用户打开程序可能需要使用一段时间才会关闭。那么当用户打开程序时,我们首先就需要插入一条日志记录,记录下当前用户打开的

    2024年02月11日
    浏览(43)
  • 如何将Excel数据导入到MS SQL Server已定义的数据表中

    1. 前提:已经在SQL Server中定义了所要导入数据的表(如stock,salvaging和out_stock三张表)的结构。 2. 如图,选择要导入数据的数据库(例如dlqx数据库),右键-任务-导入数据。  2. 进入向导后,点击“下一步”。  3. (1)选择 数据源 ,这里选择Microsoft Excel,如图所示 (2)点击

    2024年02月04日
    浏览(42)
  • 【SQL Server】数据库开发指南(七)MS-SQL存储过程全面解析:种类、优点和创建方法详解

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2024年02月07日
    浏览(92)
  • 【SQL Server】数据库开发指南(八)高级数据处理技术 MS-SQL 事务、异常和游标的深入研究

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2024年02月07日
    浏览(86)
  • 【SQL Server】数据库开发指南(九)详细讲解 MS-SQL 触发器的的创建、修改、应用与适用场景

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2024年02月08日
    浏览(76)
  • 【SQL开发实战技巧】系列(三十四):数仓报表场景☞如何对数据分级并行转为列

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2023年04月09日
    浏览(47)
  • 【SQL】对表中的记录通过时间维度分组,统计出每组的记录条数

    场景:一般用作数据统计,比如统计一个淘宝用户在年、月、日的维度上的订单数。 业务:一个集合,以时间维度来进行分组求和。 准备一张订单表order,有一些常规属性,比如创建时间,订单号。 DDL语句如下: 测试数据准备如下,50条DML语句,其中order_date分布在2023年1月

    2024年01月16日
    浏览(40)
  • SQL Server问题记录

    连接SQL Server失败,默认使用的驱动是sqljdbc4: 结果报错信息如下: 参考DataGrip连接SQL Server,提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server建立安全连接的解决方法 结果使用Microsoft SQL Server(jTds)之后,报错信息如下: 参考解决方法。 新建文件 custom.java.security ,

    2024年02月04日
    浏览(27)
  • 【SQL SERVER】SQL SERVER无法连接到服务器解决过程记录

    很久没用sql server了,这几天打算更新一下 这篇文章,发现连接不上服务器。 找一下解决办法。 一、打开服务界面 在键盘上按“WIN+R”快捷键,打开运行界面,输入“services.msc”指令,按“确定”; 二、找到SQL这一堆,全部启动 打开之后还是不行,之后又重启电脑还是不行

    2024年02月20日
    浏览(53)
  • Sql Server类型转换,Sql Server函数

    CONVERT(数据类型, 表达式, 样式)    --样式可以没有 CAST(表达式 as 数据类型) DateOfBirth是DateTime类型,不转换会报错 ------------------函数----------------- 计算日期差:datediff(日期格式,日期1, 日期2) 向下取整:Floor() 样例:

    2024年02月16日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包