【新星计划】数据库行列转换初识

古早时代

在很久很久以前，有。。。走错片场了。。。

在很早的时候，数据库操作，t-sql 规范里就有了两个指令 join 和 union，相信小伙伴们都知道这些是干什么用的。

join 可以将多个表横向关联，可以用来做行转列，而 union 可以将多个结果集合并，可以用来做列转行。

那么古早时代的行列转换就是这么完成的了。以 mssql 为例，可以看看实现方式。

CSDN 文盲老顾的博客，https://blog.csdn.net/superwfei

行转列，将 type 数据转成列名

select p.*,A,B,D,E 
from (
	select number,name as P 
	from master..spt_values 
	where number between 0 and 10 and type='P'
) p
full outer join (
	select number,name as A 
	from master..spt_values 
	where number between 0 and 10 and type='A'
) a on p.number=a.number
full outer join (
	select number,name as B 
	from master..spt_values 
	where number between 0 and 10 and type='B'
) b on p.number=b.number
full outer join (
	select number,name as D 
	from master..spt_values 
	where number between 0 and 10 and type='D'
) d on p.number=d.number
full outer join (
	select number,name as E
	from master..spt_values 
	where number between 0 and 10 and type='E'
) e on p.number=e.number

列转行，将 high 和 low 两列转成行

select number,type,low as num,'low' as bt from master..spt_values where low is not null and number between 1 and 10
union all
select number,type,high,'high' as bt from master..spt_values where high is not null and number between 1 and 10
order by number,type

聚合函数结合条件函数

后来，在不知道哪个年代，聚合函数结合条件判断，也被用来做了行转列。毕竟，一般需要用到行转列的地方，都是报表之类的统计方向。

select [type],count(0) cnt
	,sum(case when number<0 then 1 else 0 end) 小于0 
	,sum(case when number>0 then 1 else 0 end) 大于0 
	,sum(case when number=0 then 1 else 0 end) 等于0 
	,sum(case when low is null then 0 else 1 end) low有数据
	,sum(case when high is null then 0 else 1 end) high有数据
from master..spt_values 
group by type
order by 1

而列转行，就没有特别的发展，还是需要用到 union。

使用子查询和计算列进行附加列

除了用 join 追加列之外，刚才我们还用到了计算列，比如聚合函数追加列，其实除了聚合函数，其他函数也都可以用以计算一些数据来增加列，子查询可以当做特殊的计算方式。比如字符串截取，列计算之类的。比如，判断是否有符合条件的数据，使用 case 语句生成特例数据。在leetcode 608题，就是这样一个方式。

而在 mssql 里，还有一个新的查询指令 apply ，包括 cross apply/outer apply ，也可以用来追加列，且可以使用更多的列，相比 join 无法引用原表的数据，子查询只能追加一个列，apply 则可以引用原表的数据，且可以同时追加多个列，并且也可以使用计算方式。

sqlserver pivot/unpivot

在行列转换需求日益增加的今天，mssql 对行列转换专门做了一个指令进行支持，也就是标题中的 pivot / unpivot。其实看到这些关键字，自行百度，就可以学到基础用法了。

老顾在《Sql 语句小课堂6：使用开窗函数和系统表生成一个简单的日历》的文章中，就是使用的 pivot ，行转列来完成的查询。

列转行使用 unpivot

语法也很简单

select * 
from sourceTable a
unpivot(val for col in (原查询中的列集合)) p -- 列转行查询别名，该语句返回一个表值结果，并替代原表中，出现在列转行中的列

val 作为列转行后的值列，col 作为列名列，对于 null 值，在列转行时，默认是无该行数据的，所有其他未出现在 unpivot 中的列，作为重复数据出现，每行生成的数据，都有这些重复数据。

行转列使用 pivot

语法也差不多，不过值需要进行聚合

select *
from sourceTable a
pivot(max(val) for col in (col列的值集合)) p -- 行转列查询别名，该语句返回一个表值结果，并替代原表中，val和col列，按照聚合，将 col 的值作为列名，val 的值填充到对应列中

val 列的值作为值，col 列的值作为列名，产生新的列，对于非 val 和 col 列，则进行排重合并，如果不能排重合并，则产生多行数据。

嗯，这里用 val 和 col 代指，实际使用中，可以用各种列名来代替。

动态生成行列转换 sql 指令

不管是 mssql 也好，mysql 也好，其实我们都可以得到某个表的表结构数据。

然后，我们就可以在存储过程中，根据这个表的结构，来动态生成行转列，或者列转行的查询指令。

这个留给小伙伴们自行思考，或者百度一下，看看其他小伙伴的实现方式。

小结

行转列的方式还是比较多的，看各自的需求，以不同的方式实现即可。

列转行，目前除了 union 之外，好像也只能用 unpivot 来实现，嗯，如果有其他支持表值函数的数据环境，应该也可以用其他方式实现。

mysql 的最大问题就是不支持表值函数查询结果。所以，mysql 即没有 pivot/unpivot，也没有 cross apply/outer apply。对于习惯用表值结果来扩充数据的人来说，这真是痛苦的一匹啊。

文后语

本文本来是打算在腾讯会议分享时，作为大纲的内容，但由于身体原因，文章也没及时完成，分享课也没能如期举行，还望大家多多包涵。

如有问题，还请在赛道群内向老顾留言，老顾会及时回复的。赛道入口地址：https://bbs.csdn.net/topics/614902402，扫码进入赛道群即可。