SQL 列转行、行转列 - 使用sqlite演示

一 列转行

CREATE TABLE test1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           VARCHAR(50)    NOT NULL,
   语文 INT,
   数学 INT,
   英语 INT
);

insert into test1 values (1,'张飞',80,90,100);
insert into test1 values (2,'赵飞燕',100,90,80);

SELECT
    NAME,
    '语文' AS subject ,
    MAX("语文") AS score
FROM test1 GROUP BY NAME
UNION
SELECT
    NAME,
    '数学' AS subject ,
    MAX("数学") AS score
FROM test1 GROUP BY NAME
UNION
SELECT
    NAME,
    '英语' AS subject ,
    MAX("英语") AS score
FROM test1 GROUP BY NAME

使用如上SQL就对了；下面是网上的，

select *
from (select sname,[Course ] ='数学',[Score]=[数学] from Tb_students union all
select sname,[Course]='英语',[Score]=[英语] from Tb_students union all
select sname,[Course]='语文',[Score]=[语文] from Tb_students)t
order by sname,case [Course] when '语文' then 1 when '数学' then 2 when '英语' then 3 end

有些问题；

参阅

https://www.cnblogs.com/injet/p/10885478.html

二 行转列

CREATE TABLE test2(
   NAME           VARCHAR(50)    NOT NULL,
   subject varchar(50),
   score int
);

insert into test2 values ('小明','语文',96);
insert into test2 values ('小明','数学',98);
insert into test2 values ('小明','英语',95);
insert into test2 values ('大花','语文',92);
insert into test2 values ('大花','数学',96);
insert into test2 values ('大花','英语',98);

SELECT name,
  MAX(
  CASE
    WHEN subject='语文'
    THEN score
    ELSE 0
  END) AS "语文",
  MAX(
  CASE
    WHEN subject='数学'
    THEN score
    ELSE 0
  END) AS "数学",
  MAX(
  CASE
    WHEN subject='英语'
    THEN score
    ELSE 0
  END) AS "英语"
FROM test2
GROUP BY name

 

使用SQLITE；

三 PIVOT

如果是SQL Server 则可使用PIVOT 和 UNPIVOT；

PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。
PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

语法形式：　　　  
SELECT 
<非透视的列>, [第一个透视的列] AS <列名称>,[第二个透视的列] AS <列名称>,...[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>) AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>

SELECT *
FROM student
PIVOT (
    SUM(score) FOR subject IN (语文, 数学, 英语)
)

SELECT *
FROM student1
UNPIVOT (
    score FOR subject IN ("语文","数学","英语")
)