sqlserver行转列和列转行.docx
《sqlserver行转列和列转行.docx》由会员分享，可在线阅读，更多相关《sqlserver行转列和列转行.docx（22页珍藏版）》请在冰豆网上搜索。
sqlserver行转列和列转行
1：
行转列
子查询，获取一定数据集结果
SELECTobjid,action,count
（1）AS[count]FROMT_MyAttentionWHEREobjidIN
（SELECTTOP10objidFROMT_MyAttentiontmaGROUPBYobjidORDERBYcount
（1）DESC）
GROUPBYobjid,action
下面用行转列语法获取最终结果
select*
from
（
SELECTobjid,action,count
（1）AS[count]FROMT_MyAttentionWHEREobjidIN
（SELECTTOP10objidFROMT_MyAttentiontmaGROUPBYobjidORDERBYcount
（1）DESC）
GROUPBYobjid,action
）t
pivot（sum（count）forin（[1],[2],[3],[4]））asourpivot
微软官方的图：
2：
列转行
怎么把一条记录拆分成几条记录？
User No. A B C
1 1 21 34 24
1 2 42 25 16
RESULT:
User No. Type Num
1 1 A 21
1 1 B 34
1 1 C 24
1 2 A 42
1 2 B 25
1 2 C 16
declare@ttable（usserint,noint,aint,bint,cint）
insertinto@tselect1,1,21,34,24
unionallselect1,2,42,25,16
SELECTusser,no,Type=attribute,Num=value
FROM@t
UNPIVOT
（
valueFORattributeIN（[a],[b],[c]）
）ASUPV
列转行备注valueFORattributeIN（[a],[b],[c]）这句话中，a,b,c是列的名字，但是列名不能出如今上句的select语句中。
-结果
/*
usser no Type num
---- --- -------- --------
1 1 a 21
1 1 b 34
1 1 c 24
1 2 a 42
1 2 b 25
1 2 c 16
*/
T-SQL语句中，PIVOT命令可以实现数据表的列转行，UNPIVOT那么与其相反，实现数据的行转列。
本文结合实例说明了这一过程，希望能对您有所帮助。
AD：
WOT2021互联网运维与开发者大会热销抢票
一、使用PIVOT和UNPIVOT命令的SQLServer版本要求
1.数据库的最低版本要求为SQLServer2005或更高。
2.必须将数据库的兼容级别设置为90或更高。
3.查看我的数据库版本及兼容级别。
假设不知道怎么看数据库版本或兼容级别的话可以在SQLServerManagementStudio新建一个查询窗口输入：
print@@version，运行之后在我的本机上得到：
Apr 2202115:
53:
02
Copyright（c）MicrosoftCorporation
ExpressEditionwithAdvancedServicesonWindowsNT5.2（Build3790:
ServicePack2）
然后我们选择一个数据库然后右键-属性选择[选项]得到以下图的信息。
在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习。
二、使用PIVOT实现数据表的列转行
1.在这里我们先构建一个测试数据表（这里使用的是临时表，以方便我们在退出会话的时候自动删除表及其数据）
首先我们先设计一个表架构为#Student{学生编号[PK], 姓名,性别,所属班级}的表,然后编写如下T-SQL
--创立临时表（仅演示,表构造的不合理还请包涵）
1.CREATE TABLE #Student （
2.
3.[学生编号] INT IDENTITY（1, 1） PRIMARY KEY,
4.
5.[姓名] NVARCHAR（20）,
6.
7.[性别] NVARCHAR
（1）,
8.
9.[所属班级] NVARCHAR（20）
10.
11.）;
--给临时表插入数据
1.INSERT INTO #Student （
2.
3.[姓名], [性别], [所属班级]
4.
5.）
6.
7.SELECT '李妹妹', '女', '初一 1班' UNION ALL
8.
9.SELECT '泰强', '男', '初一 1班' UNION ALL
10.
11.SELECT '泰映', '男', '初一 1班' UNION ALL
12.
13.SELECT '何谢', '男', '初一 1班' UNION ALL
14.
15.SELECT '李春', '男', '初二 1班' UNION ALL
16.
17.SELECT '吴歌', '男', '初二 1班' UNION ALL
18.
19.SELECT '林纯', '男', '初二 1班' UNION ALL
20.
21.SELECT '徐叶', '女', '初二 1班' UNION ALL
22.
23.SELECT '龙门', '男', '初三 1班' UNION ALL
24.
25.SELECT '小红', '女', '初三 1班' UNION ALL
26.
27.SELECT '小李', '男', '初三 1班' UNION ALL
28.
29.SELECT '小黄', '女', '初三 2班' UNION ALL
30.
31.SELECT '旺财', '男', '初三 2班' UNION ALL
32.
33.SELECT '强强', '男', '初二 1班';
以下是查询的结果：
学生编号
姓名
性别
所属班级
1
李妹妹
女
初一1班
2
泰强
男
初一1班
3
泰映
男
初一1班
4
何谢
男
初一1班
5
李春
男
初二1班
6
吴歌
男
初二1班
7
林纯
男
初二1班
8
徐叶
女
初二1班
9
龙门
男
初三1班
10
小红
女
初三1班
11
小李
男
初三1班
12
小黄
女
初三2班
13
旺财
男
初三2班
14
强强
男
初二1班
1.SELECT
2.
3.[所属班级] AS [班级],
4.
5.COUNT
（1） AS [人数]
6.
7.FROM #Student
8.
9.GROUP BY [所属班级]
10.
11.ORDER BY [人数] DESC
班级
人数
初二1班
5
初一1班
4
初三1班
3
初三2班
2
好了，在这里我希望把上面的表{班级,人数}由班级[行]的显示转换为班级[列]的显示格式!
在此你会看到第一个PIVOT例如。
是否很期待?
?
例如
1.SELECT
2.
3.'班级总人数:
' AS [总人数],
4.
5.[初一 1班], [初一 2班],
6.
7.[初二 1班],
8.
9.[初三 1班], [初三 2班]
10.
11.FROM （
12.
13.SELECT
14.
15.[所属班级] AS [班级],
16.
17.[学生编号]
18.
19.FROM #Student
20.
21.） AS [SourceTable]
22.
23.PIVOT （
24.
25.COUNT（[学生编号]）
26.
27.FOR [班级] IN （
28.
29.[初一 1班], [初一 2班],
30.
31.[初二 1班],
32.
33.[初三 1班], [初三 2班]
34.
35.）
36.
37.） AS [PivotTable]
在结果表中我们看到了对于不存在的班级初一2班它的总人数为0，这符合我们预期的结果!
解释：
使用POVIT首先你需要在FROM子句内定义2个表：
A.一个称为源表（SourceTable）。
B.另一个称为数据透视表（PivotTable）。
语法：
1.SELECT
2.
3.<未透视的列>,
4.
5.[第一个透视列] AS <列别名>,
6.
7.[第二个透视列] AS <列别名>,
8.
9....
10.
11.[最后一个透视列] AS <列别名>
12.
13.FROM （
14.
15.
16.
17.） AS <源表>
18.
19.PIVOT （
20.
21.<聚合函数>（<列>）
22.
23.FOR [<需要转换为行的列>] IN （
24.
25.[第一个透视列], [第二个透视列],
26.
27....
28.
29.[最后一个透视列]
30.
31.）
32.
33.） AS <数据透视表>
34.
35.<可选的ORDER BY子句>;
以上的PIVOT子句内的第1…n个透视列的值均为需要转换为行的列的常量值,需要用[]括起，支持GUID，字符串及各种数字!
行转列的应用例如
--使用PIVOT查询班级内的男女学生人数及总人数
1.SELECT
2.
3.[所属班级] AS [班级],
4.
5.[男] AS [男生人数],
6.
7.[女] AS [女生人数],
8.
9.[男] + [女] AS [总人数]
10.
11.FROM （
12.
13.SELECT [学生编号], [所属班级], [性别] FROM #Student
14.
15.） AS [SourceTable]
16.
17.PIVOT （
18.
19.COUNT（[学生编号]）
20.
21.FOR [性别] IN （
22.
23.[男], [女]
24.
25.）
26.
27.） AS [PivotTable]
28.
29.ORDER BY [总人数] DESC
三、使用UNPIVOT实现的功能其实与PIVOT恰恰相反
1.SELECT
2.
3.<未逆透视的列>,
4.
5.[合并后的列] AS <列别名>,
6.
7.[行值的列名] AS <列别名>
8.
9.FROM （
10.
11.
12.
13.） AS <源表>
14.
15.UNPIVOT （
16.
17.<行值的列名>
18.
19.FOR <将原来多个列合并到单个列的列名> IN （
20.
21.[第一个合并列], [第二个合并列],
22.
23....
24.
25.[最后一个合并列]
26.
27.）
28.
29.） AS <数据逆透视表>
30.
31.<可选的ORDER BY子句>;
很浮云，不怕，这里带例子（继续使用II中用到的PIVOT表）
--源表
1.SELECT
2.
3.'班级总人数:
' AS [总人数],
4.
5.[初一 1班], [初一 2班],
6.
7.[初二 1班],
8.
9.[初三 1班], [初三 2班]
10.
11.INTO #PivotTable --为了使表达意图更明晰,我把PIVOT处理后的表放到一个临时表当中
12.
13.FROM （
14.
15.SELECT
16.
17.[所属班级] AS [班级],
18.
19.[学生编号]
20.
21.FROM #Student
22.
23.） AS [SourceTable]
24.
25.PIVOT （
26.
27.COUNT（[学生编号]）
28.
29.FOR [班级] IN （
30.
31.[初一 1班], [初一 2班],
32.
33.[初二 1班],
34.
35.[初三 1班], [初三 2班]
36.
37.）
38.
39.） AS [PivotTable]
将多个列合并到单个列的转换的语句!
!
!
--结果
1.SELECT
2.
3.[班级], [总人数]
4.
5.FROM （
6.
7.SELECT
8.
9.[初一 1班], [初一 2班],
10.
11.[初二 1班],
12.
13.[初三 1班], [初三 2班]
14.
15.FROM
16.
17.#PivotTable
18.
19.） AS [s]
20.
21.UNPIVOT （
22.
23.[总人数]
24.
25.FOR [班级] IN （
26.
27.[初一 1班], [初一 2班],
28.
29.[初二 1班],
30.
31.[初三 1班], [初三 2班]
32.
33.）
34.
35.） AS [un_p]
执行下面代码：
1.SELECT
2.
3.[所属班级] AS [班级],
4.
5.[男] AS [男生人数],
6.
7.[女] AS [女生人数],
8.
9.[男] + [女] AS [总人数]
10.
11.INTO #PivotTable2 --放到临时表方便查询
12.
13.FROM （
14.
15.SELECT [学生编号], [所属班级], [性别] FROM #Student
16.
17.） AS [SourceTable]
18.
19.PIVOT （
20.
21.COUNT（[学生编号]）
22.
23.FOR [性别] IN （
24.
25.[男], [女]
26.
27.）
28.
29.） AS [PivotTable]
30.
31.ORDER BY [总人数] DESC
32.
33.SELECT
34.
35.[班级],
36.
37.[男生或女生人数],
38.
39.[性别],
40.
41.[总人数]
42.
43.FROM （
44.
45.SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2
46.
47.） AS [s]
48.
49.UNPIVOT （
50.
51.[男生或女生人数]
52.
53.FOR [性别] IN （
54.
55.[男生人数],
56.
57.[女生人数]
58.
59.）
60.
61.） AS [un_p]
或者将性别和人数合并到一个列当中：
1.SELECT
2.
3.[班级],
4.
5.[性别] + ':
' + CAST（[男生或女生人数] AS NVARCHAR
（1）） AS [男生或女生人数],
6.
7.[总人数]
8.
9.FROM （
10.
11.SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2
12.
13.） AS [s]
14.
15.UNPIVOT （
16.
17.[男生或女生人数]
18.
19.FOR [性别] IN （
20.
21.[男生人数],
22.
23.[女生人数]
24.
25.）
26.
27.） AS [un_p]
关于PIVOT和UNPIVOT命令的使用就介绍到这里，假设想理解更多SQL的知识可以去看看这里的文章：
，绝对不会让您绝望的哦！
升级会员 