sql行转列应用的动态实现方式（又要搞数据报表了）
sql行转列应用的动态实现方式（又要搞数据报表了）途径城市手机号码新建两张表，一张用户表，另一张为出行途径记录表，二者的关系为经典的一对多强耦合关系！接下里我们需要的数据格式为如下模式：流水号
导读：本章主要整理一下软件开发工程师笔试经典的数据库题目，SQL的行转列！年底了，正好也到了纷杂的编写各种年终数据报表的时刻了，趁这个机会好好复习一下该方面的经典应用，以备不时之需！
SQL的行转列应用，主要分成俩个层面的需求，第一是数据内容的转换，第二是列级别的转换！接下来让我们一一深入地学习一下：(本案例采用的是SQLSERVER数据库)
行转列之内容应用：疫情期间，相信大家去任何公共的场所，都需要检验一下行程码！这就是一个很好的应用，你手机到达基站的范围，会有数据的通信，记录你所经过的位置，最终会展现在你手机的行程码之上！
我们以这个案例为例，以SQL的形式，来重现这日常生活中的经典应用！
新建两张表，一张用户表，另一张为出行途径记录表，二者的关系为经典的一对多强耦合关系！
接下里我们需要的数据格式为如下模式：
|
流水号 |
手机号码 |
途径城市 |
|
1 |
13888888888 |
广州市，上海市，北京市 |
|
2 |
13999999999 |
南京市，苏州市 |
|
3 |
19111111111 |
杭州市，沈阳市 |
这就需要我们将多行的内容，变成一个列的内容！行的内容到列的转换，就是这种情况！在这里需要注意的是，途径地址的数据没有取值范围，是动态的存在！所以我们不能写死，需要灵活地获取！主要采用SQL递归拼接字符串的知识点，就可以为我们解决这个难题！
- 首先编写SQL函数：该函数的作用，就是将每个用户对应的途径路径全部拼接出来，然后放在SQL中进行使用！
create function GetWayList(@uid int)
returns varchar(1000)
as
begin
declare @content varchar(1000)
set @content = ''
select @content = uwayname ' ' @content from Users_Way where uid = @uid
order by uwaytime desc
if(len(@content)>0)
begin
set @content = left(@content len(@content)-1)
end
return @content
end
2.第二步直接在sql中嵌套使用即可，非常的便捷！
SELECT uid uphone dbo.GetWayList(uid) as 途径城市 from Users
结果就是我们需要的样子，怎么样看起来还不错吧！至于深度的优化，还是需要看小伙伴们的智慧了！
总结一下：
对于这方面的需求，更简单的方式SQLServer中需要XML PATH，这种方式性能很好，但一些云服务并不支持！MySQL中则有Group_concat()函数，则更加的简洁明了！
行转列的标准模式：有的情况下，我们希望看到行作为列进行展示，这样更加直观地反应数据的情况，看起来比较舒服，也比较直观！例如：一周七天的进货数据，在数据库中是七行，对于开发人员觉得很正常，但老板看起来肯定不爽，周一、周二。。。。。星期日，这种格式的展现更适合非专业人士的审美观！
这种方式主要使用的知识点：聚合函数、SQL分组统计、SQL的多分支结构等！接下来让我们结合案例，实战一下！
下图为我们的数据结构：
我们希望最终的查询结果如下：
过程分析：
首先我们先编写如下的SQL：
SELECT SNO SNAME case subject when '语文' then score else 0 end as 语文
case subject when '数学' then score else 0 end as 数学
case subject when '英语' then score else 0 end as 英语
From StudentScore
此时出现的结果为下图：但离我们要求的还是差了一步，毕竟一个人的数据，存在多行的情况！
接下来我们再进一步，使用Group By 与 聚合函数（MAX、SUM）都可以，不影响最终的统计数据结果：
SELECT SNO SNAME MAX(case subject when '语文' then score else 0 end) as 语文
MAX(case subject when '数学' then score else 0 end) as 数学
MAX(case subject when '英语' then score else 0 end) as 英语
From StudentScore
GROUP BY SNO SNAME
经过上述的努力，终于得到了我们想要的结果：
并且可以继续完善字段，搞一张漂漂亮亮的SQL版学生报表成绩单！
SELECT SNO SNAME MAX(case subject when '语文' then score else 0 end) as 语文
MAX(case subject when '数学' then score else 0 end) as 数学
MAX(case subject when '英语' then score else 0 end) as 英语
SUM(score) as '总分' CAST((SUM(score)*1.0/3) as decimal(18 2)) as 平均分
From StudentScore
GROUP BY SNO SNAME
另外SQLServer中还提供了PIVOT函数，可以实现快速的行专列操作！PIVOT函数的格式如下：PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1] [行转列后的列名2] [行转列后的列名3] .......[行转列后的列名N]))
- <聚合函数>就是我们使用的SUM COUNT AVG等Sql聚合函数，也就是行转列后计算列的聚合方式。
- [聚合列值]要进行聚合的列名
- [行转列前的列名]这个就是需要将行转换为列的列名。
SELECT SNO SNAME 语文 数学 英语 FROM StudentScore AS T
PIVOT
(
--score为汇总成绩列
--subject 为需要转换的原始列
--(语文 数学 英语) 转换成的列名
SUM(score) for subject in(语文 数学 英语)
)TBL
按照官方的语法格式进行编写后，发现了一个严重的问题，那就是行专列失败了！究其原因很简单，PIVOT函数行专列的方式，还是与我们手工编写的原理一样，只不过进行了封装，但是为PIVOT函数提供的数据源必须是干净的，有利于分组的，不能存在难以分组的列，例如自增的ID！接下来我们调整下代码，继续测试！
SELECT * FROM
(
SELECT SNO SNAME SCORE SUBJECT FROM StudentScore
)AS T1
PIVOT
(
SUM(score) for subject in(语文 数学 英语)
)T2
order by SNO
调整后，我们得到了想要的SQL结果！
其实行专列的写法，不仅逻辑上有一定的要求，并且需要具备一些空间想象力！但是贫民版的写法，只需要直接撸SQL即可，完全不需要有这些烦恼之处，但是肯定是比较笨的一种选择，有的时候急于交差，可以视为一种解决方案了！
SELECT s.sno s.sname sum(ISNULL(YuWen.score 0)) as 语文
sum(ISNULL(ShuXue.score 0)) as 数学 sum(ISNULL(YingYu.score 0)) as 英语
FROM StudentScore AS S
LEFT JOIN
(
SELECT SID SNO SCORE FROM StudentScore where subject = '语文'
)AS YuWen on s.sid = YuWen.sid
LEFT JOIN
(
SELECT SID SNO SCORE FROM StudentScore where subject = '数学'
)AS ShuXue on s.sid = ShuXue.sid
LEFT JOIN
(
SELECT SID SNO SCORE FROM StudentScore where subject = '英语'
)AS YingYu on s.sid = YingYu.sid
Group by s.sno s.sname
非常的直接，简单、粗暴的解决方式！
上述内容就是我们平时编程过程中，遇到的SQL行专列知识点！感兴趣的小伙伴们可以深入地研究一下，并且从性能的角度，给与足够的优化！
但是很多情况下，我们的列并非固定，需要根据实际情况动态生成！下一章我们聊一聊SQL动态行转列的具体应用。
喜欢的小伙伴给个点赞、转发加关注，一起交流学习！我是IT鸟叔，一位喜欢写程序、钓鱼、喝茶、玩游戏的中年大叔！
