柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 Oracle行轉(zhuǎn)列詳解

http://yzkb.51969.com/

一、建表與插入數(shù)據(jù)

1.1、建表

create table kecheng- (- id NUMBER,- name VARCHAR2(20),- course VARCHAR2(20),- score NUMBER- );- insert into kecheng (id, name, course, score)- values (1, ‘張三’, ‘語文’, 67);- insert into kecheng (id, name, course, score)- values (1, ‘張三’, ‘?dāng)?shù)學(xué)’, 76);- insert into kecheng (id, name, course, score)- values (1, ‘張三’, ‘英語’, 43);- insert into kecheng (id, name, course, score)- values (1, ‘張三’, ‘歷史’, 56);- insert into kecheng (id, name, course, score)- values (1, ‘張三’, ‘化學(xué)’, 11);- insert into kecheng (id, name, course, score)- values (2, ‘李四’, ‘語文’, 54);- insert into kecheng (id, name, course, score)- values (2, ‘李四’, ‘?dāng)?shù)學(xué)’, 81);- insert into kecheng (id, name, course, score)- values (2, ‘李四’, ‘英語’, 64);- insert into kecheng (id, name, course, score)- values (2, ‘李四’, ‘歷史’, 93);- insert into kecheng (id, name, course, score)- values (2, ‘李四’, ‘化學(xué)’, 27);- insert into kecheng (id, name, course, score)- values (3, ‘王五’, ‘語文’, 24);- insert into kecheng (id, name, course, score)- values (3, ‘王五’, ‘?dāng)?shù)學(xué)’, 25);- insert into kecheng (id, name, course, score)- values (3, ‘王五’, ‘英語’, 8);- insert into kecheng (id, name, course, score)- values (3, ‘王五’, ‘歷史’, 45);- insert into kecheng (id, name, course, score)- values (3, ‘王五’, ‘化學(xué)’, 1);- commit;

二、固定行列轉(zhuǎn)換

2.1、Decode方式

SELECT ID,NAME,- SUM(DECODE(course,‘語文’,score,0)) 語文,–這里使用max,min都可以- SUM(DECODE(course,‘?dāng)?shù)學(xué)’,score,0)) 數(shù)學(xué),- SUM(DECODE(course,‘英語’,score,0)) 英語,- SUM(DECODE(course,‘歷史’,score,0)) 歷史,- SUM(DECODE(course,‘化學(xué)’,score,0)) 化學(xué)- FROM kecheng- GROUP BY ID ,NAME

2.2、Case方式

SELECT ID,NAME,- MAX(CASE WHEN course=‘語文’ THEN score ELSE 0 END) 語文,- MAX(CASE WHEN course=‘?dāng)?shù)學(xué)’ THEN score ELSE 0 END) 數(shù)學(xué),- MAX(CASE WHEN course=‘英語’ THEN score ELSE 0 END) 英語,- MAX(CASE WHEN course=‘歷史’ THEN score ELSE 0 END) 歷史,- MAX(CASE WHEN course=‘化學(xué)’ THEN score ELSE 0 END) 化學(xué)- FROM kecheng- GROUP BY ID ,NAME

2.3、wmsys.wm_concat行列轉(zhuǎn)換函數(shù)

SELECT ID,NAME, wmsys.wm_concat(course || ‘:’||score) course FROM kecheng GROUP BY ID ,NAME;

2.4、使用over(partition by t.u_id)用法

SELECT NAME, wmsys.wm_concat(course ||score) OVER (PARTITION BY NAME) FROM kecheng

三、動(dòng)態(tài)轉(zhuǎn)換

3.1、使用PL/SQL

CREATE OR REPLACE PROCEDURE P_TOYGSEND IS- –存放最終的SQL- LV_SQL VARCHAR2(3000);- –存放連接的SQL- SQL_COMMOND VARCHAR2(3000);- –定義游標(biāo)- CURSOR CUR IS- SELECT COURSE FROM KECHENG GROUP BY COURSE;- BEGIN- –定義查詢開頭- SQL_COMMOND := 'SELECT NAME ';

FOR I IN CUR- LOOP- –將結(jié)果相連接- SQL_COMMOND := SQL_COMMOND || ’ ,SUM(DECODE(course,‘’’ || I.COURSE ||- ‘’',score,0)) ’ || I.COURSE;- END LOOP;

SQL_COMMOND := SQL_COMMOND || ’ from KECHENG group by name’;

LV_SQL := 'INSERT INTO temp_ss ’ || SQL_COMMOND;

EXECUTE IMMEDIATE LV_SQL;- commit;- EXCEPTION- WHEN OTHERS THEN- ROLLBACK;- NULL;-

END P_TOYGSEND;

轉(zhuǎn)自：https://blog.csdn.net/w892824196/article/details/82222193

柚子快報(bào)邀請(qǐng)碼778899分享：數(shù)據(jù)庫 Oracle行轉(zhuǎn)列詳解

http://yzkb.51969.com/

文章來源