SQL行转列，列转行

行列转换在做报表分析时还是经常会遇到的，今天就说一下如何实现行列转换吧。

行列转换就是如下图所示两种展示形式的互相转换

假如我们有下表：

通过上面 SQL 语句即可得到下面的结果

PIVOT 后跟一个聚合函数来拿到结果，FOR 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。

当然我们也可以用 CASE WHEN 得到同样的结果，就是写起来麻烦一点。

使用 CASE WHEN 可以得到和 PIVOT 同样的结果，没有 PIVOT 简单直观。

假设我们有下表 student1

通过 UNPIVOT 即可得到如下结果：

我们也可以使用下面方法得到同样结果

在 SQL 任务里面经常会遇到一列转多行的需求,下面就来总结一下在 Flink SQL 里面如何实现列转行的,先来看下面的一个具体案例.

原始数据格式如下:

现在希望得到的数据格式是这样的:

这是一个典型的列转行或者一行转多行的场景,需要将 data 列进行拆分成为多行多列,下面介绍两种实现方式.

这里在定义 data 字段类型的时候直接定义为 ARRAY 类型,因为 unnest 函数需要一个数组类型的参数.

自定义表值函数（UDTF），自定义表值函数，将 0 个、1 个或多个标量值作为输入参数（可以是变长参数）。与自定义的标量函数类似，但与标量函数不同。表值函数可以返回任意数量的行作为输出，而不仅是 1 个值。返回的行可以由 1 个或多个列组成。调用一次函数输出多行或多列数据。必须继承 TableFunction 基类,并实现一个或者多个名为 eval 的方法, 在使用 UDTF 时,需要带上 LATERAL TABLE两个关键字.

自定义 UDTF 解析的时候,就不需要把 data 字段定义成 ARRAY 类型了,直接定义成 STRING 类型就可以了,并且这种方式会更加的灵活,比如还需要过滤数据或者更复杂的一些操作时都可以在 UDTF 里面完成.

unnest 和 自定义 UDTF 函数在使用的时候都有 3 种写法,前面两种写法的效果其实是一样的,第三种写法相当于 left join 的用法.区别在于 CROSS JOIN/INNER JOIN: 对于左侧表的每一行，右侧 UDTF 不输出，则这一行不输出.LEFT JOIN: 对于左侧表的每一行，右侧 UDTF 不输出，则这一行会输出，右侧 UDTF 字段为 null

在实际使用的时候如果 unnest 可以满足需求就直接用 unnest 不需要带来额外的开发,如果 unnest 函数满足不了需求,那么就自定义 UDTF 去完成.

-- ========================= PIVOT 行列转置 ===========================

-- 1、【行列转置PIVOT】

declare @Score table(StuNo varchar(10), StuName varchar(50), CourseName varchar(50), Score int)

insert into @Score

select '1', 'Tom', 'Math', 80 union all

select '1', 'Tom', 'English', 82 union all

select '1', 'Tom', 'Geography', 84 union all

select '2', 'Jone', 'Math', 79 union all

select '2', 'Jone', 'English', 88 union all

select '2', 'Jone', 'Geography',86

select * from @Score

SELECT StuNo, StuName, Math, English, [Geography]

FROM @Score PIVOT (MAX(Score) FOR CourseName in (Math, English, [Geography]) ) AS ScoreList

ORDER BY StuNo

-- 2、【列行转置UNPIVOT】

declare @ScoreList table(StuNo varchar(10), StuName varchar(50), Math int, English int, [Geography] int)

insert into @ScoreList

select '1', 'Tom', 80, 82, 84 union all

select '2', 'Jone', 79, 88, 86

select * from @ScoreList

SELECT StuNo, StuName, CourseName, Score

FROM @ScoreList UNPIVOT (Score FOR CourseName in (Math, English, [Geography]) ) AS ScorePvtTable

ORDER BY StuNo

---