sql中行转列 列转行

场景

在平常使用中，我们经常会遇到行专列，列转行进行查询的操作 或者 一行转多列的情况

我们接下来讨论如何通过一条语句实现，动态sql创建的方式暂时除外，当然你可以可以使用动态sql创建为存储过程，我会介绍几种常用的方式

我们使用pgsql数据库进行演示（其中其他库特有的我会标注，pgsql中要用的话可能不支持，但是可以使用case when then 模拟,其实就是union和case配合）

表结构

Name	Subject	Score
小明	语文	30
小明	数学	22
小明	英语	66
小花	语文	30
小花	数学	33
小花	英语	67

列转行：

使用union

SELECT Name, Subject, Score FROM T

WHERE Name='小明' and Subject='语文'

UNION ALL

SELECT Name, Subject, Score FROM T WHERE Name='小明' and Subject='数学'

UNION ALL

SELECT Name, Subject, Score FROM T WHERE Name='小明' and Subject='英语'

UNION ALL

SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='语文'

UNION ALL

SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='数学'

UNION ALL

SELECT Name, Subject, Score FROM T WHERE Name='小花' and Subject='英语'

UNPIVOT(sqlserver中)

在sqlserver中还有一种做法

SELECT *

FROM T

UNPIVOT (

score FOR subject IN (语文, 数学, 英语)

) -- score是显示的内容  subject 是需要行转列的列 in (fiel1,file2) 列的值 

多列转多行

我们还会遇到处理数据判断类型的情况，此时我们可以使用values()函数 

VALUES提供了一种生成“常量表”的方法，它可以被使用在一个查询中而不需要实际在磁盘上创建一个表。语法是：

VALUES ( 
expression [, ...] ) [, ...]

eg：VALUES (1, 'one'), (2, 'two'), (3, 'three');

我们在使用时，肯定时要起一个别名来使用

SELECT * FROM

(

VALUES (1, 'one'), (2, 'two'), (3, 'three')

) AS t (num,letter); --t代表table  (num,letter)是对应字段

等价于：

SELECT

1 AS column1,

'one' AS column2

UNION ALL

SELECT 2, 'two'

UNION ALL

SELECT 3, 'three';

 例子:

SELECT col1, col2, col3

FROM (

VALUES ('A', 'B', 'C'), ('D', 'E', 'F')

) AS my_table (col1, col2, col3); 

result:

col1 | col2 | col3

-------------------

A | B | C

D | E | F

eg:

 WITH my_table AS

(

SELECT 'A' AS col1, 'B' AS col2, 'C' AS col3

) SELECT column_name FROM my_table

UNPIVOT ( column_name FOR col IN (col1, col2, col3) ) AS unpvt;

result:

column_name

-------------

A

B

C

eg：

SELECT v.val

FROM (

VALUES ('A', 'B', 'C')

) AS t (col1, col2, col3)

CROSS APPLY (

VALUES (col1), (col2), (col3)

) AS v (val);

result:

col

-----

A

B

C

当然在这个基础上也可以增加条件

eg:选择输出A,C

SELECT v.val FROM (VALUES ('A', 'B', 'C')) AS t (col1, col2, col3) CROSS APPLY (VALUES (col1), (col2), (col3)) AS v (val) WHERE v.val IN ('A', 'C');

注意APPLY的sqlserver特有，pg中对应LATERAL 这个两个函数我会在单独的文章中介绍 地址

eg: 示例：CROSS JOIN LATERAL

SELECT v.val FROM (VALUES ('A', 'B', 'C')) AS t (col1, col2, col3) CROSS JOIN LATERAL (VALUES (col1), (col2), (col3)) AS v (val) WHERE v.val IN ('A', 'C');

行转列 ：

使用分组

select name,

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 T group by name;

这种方式有条件限制，最好保证查询结果每个科目只有一个数据 ，还可以增加总计，平均值等数据

PIVOT(sqlserver中)

select  *

FROM [StudentScores] /*数据源*/

AS P

PIVOT (

SUM(Score/*行转列后 列的值*/)

FOR p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)

) AS T