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

Open the first article

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

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

1、静态行转列

--静态的行转列
--新建一个科目成绩表
--三个字段：学生名称，科目，成绩
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)

--行转列
SELECT StuName,
ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',
ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学'
FROM SubjectScore
GROUP BY StuName

--如果加了个英语科目怎么办？没错，代码要改
--下面我们加个英文科目
INSERT INTO SubjectScore
VALUES(N'诸葛',N'英语',66)

--修改代码后行转列
SELECT StuName,
ISNULL(SUM(case SubjectName when N'语文' then isnull(Fraction,0)end),0)as N'语文',
ISNULL(SUM(case SubjectName when N'数学' then isnull(Fraction,0)end),0)as N'数学',
ISNULL(SUM(case SubjectName when N'英语' then isnull(Fraction,0)end),0)as N'英语'
FROM SubjectScore
GROUP BY StuName

　　结果：

　　　　　　

2、静态列转行

--静态列转行
--表结构：我们建几个中文的字段：学生名称，语文，数学
CREATE TABLE Demo_Stu
(
    学生名称 nvarchar(20),
    语文 decimal(16,2),
    数学 decimal(16,2)
)

--一样插入4条数据
INSERT INTO Demo_Stu
VALUES(N'孔子',88,99)

INSERT INTO Demo_Stu
VALUES(N'孔子',99,69)

INSERT INTO Demo_Stu
VALUES(N'诸葛',88,55)

INSERT INTO Demo_Stu
VALUES(N'诸葛',77,63)

--我们可以用union all 来实现,
--不了解 union all怎么用的兄弟请自行百度
select * from  (
    select 学生名称 as StuName,Subject=N'语文',Fraction=语文 from Demo_Stu
    union all
    select 学生名称 as StuName,Subject=N'数学',Fraction=数学 from Demo_Stu
)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、动态列转行

--动态态列转行
--表结构：我们建几个中文的字段：学生名称，语文，数学
CREATE TABLE Demo_Stu
(
    学生名称 nvarchar(20),
    语文 decimal(16,2),
    数学 decimal(16,2)
)

--一样插入4条数据
INSERT INTO Demo_Stu
VALUES(N'孔子',88,99)

INSERT INTO Demo_Stu
VALUES(N'孔子',99,69)

INSERT INTO Demo_Stu
VALUES(N'诸葛',88,55)

INSERT INTO Demo_Stu
VALUES(N'诸葛',77,63)


--列传行
  --列转行的方案来源于：Joe.TJ
  --列转行的动态方案：UNPIVOT,sql2005及以后版本
  --因为行是动态所以这里就从INFORMATION_SCHEMA.COLUMNS视图中获取列来构造行，同样也使用了XML处理。
  DECLARE @SQL NVARCHAR(4000)=N'';
 SET @SQL=STUFF((SELECT N','+QUOTENAME(COLUMN_NAME ) FROM INFORMATION_SCHEMA.COLUMNS
 WHERE ORDINAL_POSITION>1 AND TABLE_NAME='Demo_Stu'

 FOR XML PATH('')),1,1,N'')
 SET @SQL=N'SELECT *
          from dbo.Demo_Stu
          unpivot(分数 for 科目 in('+@SQL+'))as up';
 EXEC SP_EXECUTESQL @SQL;

 　　总结：

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

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