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

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

• 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 SubjectScore13 VALUES(N'孔子',N'语文',99)14 15 INSERT INTO SubjectScore16 VALUES(N'孔子',N'数学',80)17 18 INSERT INTO SubjectScore19 VALUES(N'诸葛',N'语文',75.5)20 21 INSERT INTO SubjectScore22 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 SubjectScore29 GROUP BY StuName30 31 --如果加了个英语科目怎么办？没错，代码要改32 --下面我们加个英文科目33 INSERT INTO SubjectScore34 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 SubjectScore42 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_Stu12 VALUES(N'孔子',88,99)13 14 INSERT INTO Demo_Stu15 VALUES(N'孔子',99,69)16 17 INSERT INTO Demo_Stu18 VALUES(N'诸葛',88,55)19 20 INSERT INTO Demo_Stu21 VALUES(N'诸葛',77,63)22 23 --我们可以用union all 来实现,24 --不了解 union all怎么用的兄弟请自行百度25 select * from  (26     select 学生名称 as StuName,Subject=N'语文',Fraction=语文 from Demo_Stu27     union all28     select 学生名称 as StuName,Subject=N'数学',Fraction=数学 from Demo_Stu29 )p order by StuName

　　结果：

　　　　         

3、动态行转列

--动态的行转列 --科目成绩表--三个字段：学生名称，科目，成绩CREATE TABLE SubjectScore (    StuName nvarchar(20),    SubjectName nvarchar(20),    Fraction decimal(16,2))--插入4条数据INSERT INTO SubjectScoreVALUES(N'孔子',N'语文',99)INSERT INTO SubjectScoreVALUES(N'孔子',N'数学',80)INSERT INTO SubjectScoreVALUES(N'诸葛',N'语文',75.5)INSERT INTO SubjectScoreVALUES(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 SubjectNameSET @sql_str = 'SELECT * FROM SubjectScore p PIVOT(     SUM([Fraction]) FOR [SubjectName] IN('+@sql_col+')) pvtorder by StuName'EXEC (@sql_str)--如果加了个英语科目怎么办？代码不需要改动--下面我们加个历史科目，再执行上面的行转列代码INSERT INTO SubjectScoreVALUES(N'诸葛',N'历史',88)INSERT INTO SubjectScoreVALUES(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_Stu12 VALUES(N'孔子',88,99)13 14 INSERT INTO Demo_Stu15 VALUES(N'孔子',99,69)16 17 INSERT INTO Demo_Stu18 VALUES(N'诸葛',88,55)19 20 INSERT INTO Demo_Stu21 VALUES(N'诸葛',77,63)22 23 24 --列传行25   --列转行的方案来源于：Joe.TJ26   --列转行的动态方案：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.COLUMNS30  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_Stu35           unpivot(分数 for 科目 in('+@SQL+'))as up';36  EXEC SP_EXECUTESQL @SQL;

 　　总结：

　　　　　　无论是静态的行转列或列转行都是会增加代码的维护性。

　　　　　　建议使用动态的行转列和列转行，可减少代码的维护性，多留一手也是比较好的。