Sql server 中将数据行转列列转行（一）

在做一些数据分析与数据展示时，经常会遇到行转列，列转行的需求，今天就来总结下：

在开始之前，先来创建一个临时表，并且写入一些测试数据：

/*
    第一步：创建临时表结构
*/
CREATE TABLE #Student  --创建临时表
(
    StuName nvarchar(20),    --学生名称
    StuSubject nvarchar(20),--考试科目
    StuScore int            --考试成绩
)
DROP TABLE #Student      --删除临时表
SELECT * FROM #Student   --查询所有数据




/*
    第二步：写入测试数据
*/
--张三
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','语文',80);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','数学',75);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','英语',65);
--李四
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','语文',36);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','数学',56);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','英语',38);
--王五
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','语文',69);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','数学',80);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','英语',78);
--赵六
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','语文',80);  
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','数学',80);
INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','英语',95);

数据准备好了之后，开始今天的正题：

 

一：行转列，下面是转换之前与之后的截图对比

 

 方法1：使用Case when 方式

SELECT StuSubject
    ,SUM(CASE WHEN StuName='张三' THEN StuScore END) as '张三'
    ,SUM(CASE WHEN StuName='王五' THEN StuScore END) as '王五'
    ,SUM(CASE WHEN StuName='赵六' THEN StuScore END) as '赵六'
FROM #Student
GROUP BY StuSubject

适用场景：要转换成多少列确定，比如上面，已经确切知道只有张三、李四、王五、赵六 四个人；

缺点：1.如果有20个人，要写20个CASE 判断，写起来恶心，代码不优雅；

           2.无法解决列是动态产生的问题，比如按月份日期转换2月有可能28天，其它月份30天；

 

 

方法2：使用PIVOT 关键字

SELECT *
FROM #Student
PIVOT(SUM(StuScore) FOR [StuName] IN("李四","王五","张三","赵六")) AS T 

适用场景：要转换成多少列确定，比如上面，已经确切知道只有张三、李四、王五、赵六 四个人；

缺点：1.无法解决列是动态产生的问题，比如按月份日期转换2月有可能28天，其它月份30天；

 

方法3：使用PIVOT、EXEC关键

Declare @StuName varchar(100);
Declare @sql nvarchar(4000)
--步骤1.假设列不固定，是动态产生的，需要先将所有列组合成一个长字符串，比如A，B，C ，
SELECT  @StuName = STUFF(
        (SELECT ',' + DS_description
         FROM Base_SalaColumn
         WHERE  DS_type='3' AND DS_means!='不参与'
         FOR xml path('')
        ),1,1,''
        )  

Print @StuName
--步骤2.由于动态产生的列，脚本不能执行，所以用Exec来执行，把脚本写成一个字符串。
SET @sql='
    select * from (select ff_BranchName 部门,ff_WorkerCode 工号,ff_workerName 姓名,ff_SFZ 身份证,ff_GZY 月份,ff_Money,ff_Name from cwc_Detail_FaFang) tab
    pivot
    (
    sum(ff_Money) for ff_Name IN('+@StuName+')
    ) pvt
'
Print @sql
--步骤3.执行脚本
Exec(@sql)

 

 

适用场景：可以解决列是动态产生的问题，不然这样的写法，太曲线救国了，很长一堆代码，

                   管你2月是28天还是100天，只要在步骤1中拼接起来就搞定了；

 

这一篇介绍了如何把数据行转换成数据列，下一篇介绍如果把数据列转换成数据行：https://www.cnblogs.com/Rawls/p/11027605.html