Oracle行转列，列转行

1.行转列

--建表语句
create table KECHENG(
    ID     VARCHAR2(200),    
    NAME   VARCHAR2(200),
    COURSE VARCHAR2(200),
    SCORE  VARCHAR2(200)
)；
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('1','张三','语文','90');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('2','张三','数学','80');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('3','张三','英语','70');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('4','张三','历史','60');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('5','张三','生物','58');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('6','张三','物理','50');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('7','李四','语文','96');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('8','李四','数学','93');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('9','李四','英语','85');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('10','李四','历史','43');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('11','李四','生物','29');
INSERT INTO KECHENG (ID, NAME, COURSE, SCORE) VALUES ('12','李四','物理','69');

(1)decode+sum(max,min)

  select ID,NAME,
       sum(DECODE(COURSE,'语文',SCORE)) 语文,
       sum(DECODE(COURSE,'数学',SCORE)) 数学,
       sum(DECODE(COURSE,'英语',SCORE)) 英语,
       sum(DECODE(COURSE,'历史',SCORE)) 历史,
       sum(DECODE(COURSE,'化学',SCORE)) 化学
       FROM KECHENG group by ID,NAME ORDER BY ID;

(2)case+sum(max,min)

select ID,NAME,
        sum(case when COURSE='语文' then SCORE end ) 语文,
        sum(case when COURSE='数学' then SCORE end ) 数学,
        sum(case when COURSE='英语' then SCORE end ) 英语,
        sum(case when COURSE='历史' then SCORE end ) 历史,
        sum(case when COURSE='化学' then SCORE end ) 化学
       FROM KECHENG group by ID,NAME ORDER BY ID;

(3)povit

select ID,NAME,语文,数学,英语 from KECHENG pivot(max(SCORE) for COURSE 
in ('语文' as 语文,'数学' 数学 ,'英语' 英语,'历史' 历史,'化学' 化学)) order by ID;

(4)wm_concat

SELECT wm_concat(COURSE||SCORE) FROM KECHENG GROUP BY ID,NAME;

2.列转行

--建表语句
create table KECHENGLZH
(
    ID   NUMBER,
    NAME VARCHAR2(20),
    "语文" NUMBER,
    "数学" NUMBER,
    "英语" NUMBER,
    "历史" NUMBER,
    "化学" NUMBER
)

INSERT INTO SCOTT.KECHENGLZH (ID, NAME, "语文", "数学", "英语", "历史", "化学") VALUES (3, '王五', 24, 25, 8, 45, 1);
INSERT INTO SCOTT.KECHENGLZH (ID, NAME, "语文", "数学", "英语", "历史", "化学") VALUES (1, '张三', 67, 76, 43, 56, 11);
INSERT INTO SCOTT.KECHENGLZH (ID, NAME, "语文", "数学", "英语", "历史", "化学") VALUES (2, '李四', 54, 81, 64, 93, 27);

(1)unpovit

select ID,NAME,COURSE,SCORE from KECHENGLZH 
unpivot(SCORE for COURSE in ( 语文, 数学 ,英语)) order by ID;