老生常谈之SQL Server （行转列，列转行）

最新推荐文章于 2024-09-09 20:25:50 发布

gongxiaojiu

最新推荐文章于 2024-09-09 20:25:50 发布

阅读量581

点赞数 1

在本文章中主要介绍以下内容：

1、静态行转列
2、静态列转行
3、动态行转列
4、动态列转行

1、静态行转列

 1 --静态的行转列
 2 --新建一个科目成绩表
 3 --三个字段：学生名称，科目，成绩
 4 CREATE TABLE SubjectScore 
 5 (
 6     StuName nvarchar(20),
 7     SubjectName nvarchar(20),
 8     Fraction decimal(16,2)
 9 )
10 
11 --插入4条数据
12 INSERT INTO SubjectScore
13 VALUES(N'孔子',N'语文',99)
14 
15 INSERT INTO SubjectScore
16 VALUES(N'孔子',N'数学',80)
17 
18 INSERT INTO SubjectScore
19 VALUES(N'诸葛',N'语文',75.5)
20 
21 INSERT INTO SubjectScore
22 VALUES(N'诸葛',N'数学',66)
23 
24 --行转列
25 SELECT StuName,
26 ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',
27 ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学'
28 FROM SubjectScore
29 GROUP BY StuName
30 
31 --如果加了个英语科目怎么办？没错，代码要改
32 --下面我们加个英文科目
33 INSERT INTO SubjectScore
34 VALUES(N'诸葛',N'英语',66)
35 
36 --修改代码后行转列
37 SELECT StuName,
38 ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',
39 ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学',
40 ISNULL(SUM(case SubjectName when N'英语' then isnull(Fraction,0)end),0)as N'英语'
41 FROM SubjectScore
42 GROUP BY StuName

　　结果：

2、静态列转行

 1 --静态列转行
 2 --表结构：我们建几个中文的字段：学生名称，语文，数学
 3 CREATE TABLE Demo_Stu
 4 (
 5     学生名称 nvarchar(20),
 6     语文 decimal(16,2),
 7     数学 decimal(16,2)
 8 )
 9 
10 --一样插入4条数据
11 INSERT INTO Demo_Stu
12 VALUES(N'孔子',88,99)
13 
14 INSERT INTO Demo_Stu
15 VALUES(N'孔子',99,69)
16 
17 INSERT INTO Demo_Stu
18 VALUES(N'诸葛',88,55)
19 
20 INSERT INTO Demo_Stu
21 VALUES(N'诸葛',77,63)
22 
23 --我们可以用union all 来实现,
24 --不了解 union all怎么用的兄弟请自行百度
25 select * from  (
26     select 学生名称 as StuName,Subject=N'语文',Fraction=语文 from Demo_Stu
27     union all
28     select 学生名称 as StuName,Subject=N'数学',Fraction=数学 from Demo_Stu
29 )p order by StuName

　　结果：

3、动态行转列

--动态的行转列 
--科目成绩表
--三个字段：学生名称，科目，成绩
CREATE TABLE SubjectScore 
(
    StuName nvarchar(20),
    SubjectName nvarchar(20),
    Fraction decimal(16,2)
)

--插入4条数据
INSERT INTO SubjectScore
VALUES(N'孔子',N'语文',99)

INSERT INTO SubjectScore
VALUES(N'孔子',N'数学',80)

INSERT INTO SubjectScore
VALUES(N'诸葛',N'语文',75.5)

INSERT INTO SubjectScore
VALUES(N'诸葛',N'数学',66)

/*
    动态行转列我们主要用到以下几个关键函数：
    2.PIVOT
    1.QUOTENAME
*/
DECLARE @sql_col NVARCHAR(4000)
DECLARE @sql_str  NVARCHAR(4000)
/*取表中行转列的所有科目*/
SELECT @sql_col = ISNULL(@sql_col+',','')+QUOTENAME(SubjectName) FROM SubjectScore GROUP BY SubjectName
SET @sql_str = '
SELECT * FROM SubjectScore p PIVOT( 
    SUM([Fraction]) FOR [SubjectName] IN('+@sql_col+')
) pvt
order by StuName
'
EXEC (@sql_str)

--如果加了个英语科目怎么办？代码不需要改动
--下面我们加个历史科目，再执行上面的行转列代码
INSERT INTO SubjectScore
VALUES(N'诸葛',N'历史',88)

INSERT INTO SubjectScore
VALUES(N'孔子',N'历史',99)

　　结果：

4、动态列转行

 1 --动态态列转行
 2 --表结构：我们建几个中文的字段：学生名称，语文，数学
 3 CREATE TABLE Demo_Stu
 4 (
 5     学生名称 nvarchar(20),
 6     语文 decimal(16,2),
 7     数学 decimal(16,2)
 8 )
 9 
10 --一样插入4条数据
11 INSERT INTO Demo_Stu
12 VALUES(N'孔子',88,99)
13 
14 INSERT INTO Demo_Stu
15 VALUES(N'孔子',99,69)
16 
17 INSERT INTO Demo_Stu
18 VALUES(N'诸葛',88,55)
19 
20 INSERT INTO Demo_Stu
21 VALUES(N'诸葛',77,63)
22 
23 
24 --列传行
25   --列转行的方案来源于：Joe.TJ
26   --列转行的动态方案：UNPIVOT,sql2005及以后版本
27   --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。
28   DECLARE @SQL NVARCHAR(4000)=N'';
29  SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS
30  WHERE ORDINAL_POSITION>1 AND TABLE_NAME='Demo_Stu'
31 
32  FOR XML PATH('')),1,1,N'')
33  SET @SQL=N'SELECT *
34           from dbo.Demo_Stu
35           unpivot(分数 for 科目 in('+@SQL+'))as up';
36  EXEC SP_EXECUTESQL @SQL;