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

╰+哭是因爲堅強的太久メ 2021-12-24 18:03 697阅读 0赞

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

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

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

/*
        第一步：创建临时表结构
    */
    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);

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

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

![404126-20190615135208798-142543052.png][]

** 方法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 ，
        下面的写法只能在SQL server2017中运行，更多将多行字段合并成一个字段方法参考：https://www.cnblogs.com/Rawls/p/10758788.html
    SELECT @StuName='"'+STRING_AGG(StuName,'","')+'"'    
    FROM (
        SELECT StuName from #Student GROUP BY StuName
    ) AS TE
    --Print @StuName
    --步骤2.由于动态产生的列，脚本不能执行，所以用Exec来执行，把脚本写成一个字符串。
    SET @sql='
        SELECT *
        FROM #Student
        PIVOT(SUM(StuScore) FOR [StuName] IN('+@StuName+')) AS T 
        '
    --步骤3.执行脚本
    Exec(@sql)

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

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

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

转载于:https://www.cnblogs.com/Rawls/p/11027413.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211223/58a5c31f0a0c40e0a29d1f9c99ed6a1a.png
[404126-20190615135208798-142543052.png]: /images/20211223/945e63ac725e4a45a454e495886f9bf4.png
[https_www.cnblogs.com_Rawls_p_11027605.html]: https://www.cnblogs.com/Rawls/p/11027605.html