python 行转列_Oracle实现行转列,列转行(经典)

已经创建的一张表,查询如图:

select * from tb_student;

实现行转列: 用decode(或者:case when ):

select name,

min(decode(course, '语文', score)) 语文,

min(decode(course, '数学', score)) 数学,

min(decode(course, '英语', score)) 英语

from tb_student

group by name;

如图:

反之: 如何用列表转行?

用union all

将结果集生成一个新表:TB_COPY

SELECT * FROM TB_COPY;

如图:

实现列转换:

select * from (select name , '语文' course,min(语文) fenshu from tb_copy group by name

union all

select name ,'数学' course ,min(数学)fenshu from tb_copy group by name

union all

select name ,'英语'course,min(英语)fenshu from tb_copy group by name )where fenshu is not null order by name

如图:

--------------------------------------------------------------------------------------------------------------补充 例子:

CREATE TABLE T_CITY(

CITYNAME VARCHAR2(16),

COURSE VARCHAR2(32),

C_NUM VARCHAR2(16)

)

INSERT INTO T_CITY VALUES('北京尚学堂','Java班','9980人');

INSERT INTO T_CITY VALUES('北京尚学堂','大数据班','2808人');

INSERT INTO T_CITY VALUES('北京尚学堂','python班','200人');

INSERT INTO T_CITY VALUES('上海尚学堂','Java班','2800人');

INSERT INTO T_CITY VALUES('上海尚学堂','大数据班','532人');

SELECT * FROM T_CITY

如图:

实现行转列:

--用case when

SELECT CITYNAME,MIN(CASE COURSE

WHEN 'Java班' THEN C_NUM END) "Java班" ,

MIN(CASE COURSE

WHEN '大数据班' THEN C_NUM END) "大数据班",

MIN(CASE COURSE

WHEN 'python班' THEN C_NUM END)"python班" FROM T_CITY GROUP BY CITYNAME;

--用 decode

SELECT CITYNAME, MIN(DECODE (COURSE,

'Java班',C_NUM)) AS "Java班",

MIN(DECODE (COURSE,

'大数据班',C_NUM)) AS "大数据班",

MIN(DECODE (COURSE,

'python班',C_NUM))AS "python班" FROM T_CITY GROUP BY CITYNAME