快捷導(dǎo)航

MS SQL Server STUFF實現(xiàn)統(tǒng)計記錄行轉(zhuǎn)為列顯示

更新時間：2024年04月09日 09:24:16 作者：?初九之潛龍勿用

SQL語句行轉(zhuǎn)列函數(shù)是一種用于將多個行數(shù)據(jù)轉(zhuǎn)換為一列的函數(shù),本文主要介紹了MS SQL Server STUFF實現(xiàn)統(tǒng)計記錄行轉(zhuǎn)為列顯示,具有一定的參考價值,感興趣的可以了解一下

范例運行環(huán)境

操作系統(tǒng)： Windows Server 2019 DataCenter

數(shù)據(jù)庫：Microsoft SQL Server 2016

視圖樣本設(shè)計

假設(shè)某一視圖 [v_pj_rep1_lname_score] 可查詢對某一被評價人的績效指標(biāo)的打分情況，并按評價人的職務(wù)進(jìn)行分類，設(shè)計如下：

序號	字段名	類型	說明	備注
1	projectcid	uniqueidentifier	項目ID
2	wxmpcid	uniqueidentifier	被評價人ID
3	count_sortid	tinyint	評價人職級排序號	數(shù)值越小職務(wù)越高
4	lname	nvarchar	評價人職務(wù)
5	rs	int	評價人總數(shù)
6	score	decimal	評價人總分	所有評價人給被評價人打分的總和
7	score2	decimal	評價人平均得分	所有評價人給被評價人打分的總和除以總?cè)藬?shù)的平均分

查詢分析器結(jié)果數(shù)據(jù)顯示如下圖：

/****** 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、評價人職務(wù)排序號進(jìn)行排序，可以看到職務(wù)越高排位越靠前。

數(shù)據(jù)統(tǒng)計要求

假設(shè)統(tǒng)計視圖名 [v_pj_rep1_lname_score_count] 可查詢對某一被評價人的所有被評價人統(tǒng)計描述（如人員人數(shù)情況、每類人打分情況等），即將視圖設(shè)計樣本的行數(shù)據(jù)變?yōu)榱羞M(jìn)行顯示，統(tǒng)計表設(shè)計如下：

序號	字段名	類型	說明	備注
1	projectcid	uniqueidentifier	項目ID
2	wxmpcid	uniqueidentifier	被評價人ID
3	score	decimal	被評價人權(quán)重分1	總?cè)藬?shù)的平均分*20%
4	score2	decimal	被評價人權(quán)重分2	總?cè)藬?shù)的平均分20%30%
5	dname	nvarchar	統(tǒng)計顯示	將行數(shù)據(jù)變?yōu)榱袛?shù)據(jù)，顯示統(tǒng)計詳情信息

查詢分析器結(jié)果數(shù)據(jù)顯示如下圖：

如圖第一行數(shù)據(jù) dname 列返回 “董事長88.21分,總經(jīng)理100.00分,分協(xié)管領(lǐng)導(dǎo)92.23分,其他領(lǐng)導(dǎo)91.79分,部門職工(2人,總分187.65分)93.83分” ，該列會顯示各職務(wù)打分的人數(shù)，總分及平均分情況，從統(tǒng)計結(jié)果來看，更加直觀。

STUFF函數(shù)實現(xiàn)

示例代碼如下：

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

關(guān)鍵說明見下表：

序號	關(guān)鍵語句	說明
1	round(sum(score2)0.2,2) score, round(sum(score2)0.2*0.3,2) score2,	取權(quán)重值，并使用ROUND函數(shù)取后兩位小數(shù)
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 函數(shù)配合 SQL 語句 FOR XML PATH 來實現(xiàn)行轉(zhuǎn)列。 SQL語句中通過 CASE 來判斷人數(shù)，大于1則顯示人數(shù)和總分，否則直接顯示分值，并在前面加上職務(wù) lname 字段，并以 count_sortid 進(jìn)行排序，數(shù)值越小的職務(wù)越往前排
3	v_pj_rep1_lname_score a group by projectcid,wxmpcid	對視圖樣本進(jìn)行項目ID和被評價人ID進(jìn)行分組統(tǒng)計

序號

關(guān)鍵語句

說明

1

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

取權(quán)重值，并使用ROUND函數(shù)取后兩位小數(shù)

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 函數(shù)配合 SQL 語句 FOR XML PATH 來實現(xiàn)行轉(zhuǎn)列。

SQL語句中通過 CASE 來判斷人數(shù)，大于1則顯示人數(shù)和總分，否則直接顯示分值，并在前面加上職務(wù) lname 字段，并以 count_sortid 進(jìn)行排序，數(shù)值越小的職務(wù)越往前排

3

v_pj_rep1_lname_score a group by projectcid,wxmpcid

對視圖樣本進(jìn)行項目ID和被評價人ID進(jìn)行分組統(tǒng)計

小結(jié)

SQL Server 中的 STUFF 函數(shù)是將字符串插入到另一個字符串中。它從第一個字符串的開始位置刪除指定長度的字符；然后將第二個字符串插入到第一個字符串的開始位置。

具體語法請參照：https://learn.microsoft.com/zh-cn/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN

至此STUFF的函數(shù)使用我們就介紹到這里，具體使用中我們還需要靈活掌握，對結(jié)果數(shù)據(jù)的細(xì)節(jié)可能要進(jìn)一步進(jìn)行處理，以滿足我們的統(tǒng)計要求。并可設(shè)計導(dǎo)出到 WORD或EXCEL文件進(jìn)行下載。

到此這篇關(guān)于MS SQL Server STUFF實現(xiàn)統(tǒng)計記錄行轉(zhuǎn)為列顯示的文章就介紹到這了,更多相關(guān)SQL 行轉(zhuǎn)為列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: