Oracle 中行列轉換問題總結

1)行轉列

表結構:STUDENT

create table STUDENT(  USER_NAME VARCHAR2(20),  COURSE    VARCHAR2(20),  SCORE     FLOAT)

初始化資料:

INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Enlish',85);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Math',75);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Chinese',97);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('zhangyue','Physics',76);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Enlish',95);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Math',67);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Physics',89);INSERT INTO STUDENT(USER_NAME,COURSE,SCORE)VALUES('heshan','Chinese',79);

初始資料如：

 

 如果需要實現如下的查詢：

 

這就是最常見的行轉列，主要原理是利用decode函數、聚集合函式(MAX)，結合group by分組實現的，MAX聚集合函式也可以用sum、min、avg等其他聚集合函式替代。

SQL代碼如下:

SELECT t.user_name,MAX(decode(course,'English',score,0)) AS English,MAX(decode(course,'Math',score,0)) AS Math,MAX(decode(course,'Chinese',score,0)) AS Chinese,MAX(decode(course,'Physics',score,0)) AS PhysicsFROM student t GROUP BY t.user_name

或者你不是用decode函數

SELECT t.user_name,MAX(case course when 'English' then score else 0 end) AS English,MAX(case course when 'Math' then score else 0 end) AS Math,MAX(case course when 'Chinese' then score else 0 end) AS Chinese,MAX(case course when 'Physics' then score else 0 end) AS PhysicsFROM student t GROUP BY t.user_name

2)多行轉換成字串

表結構:tb_name

create table tb_name(id int, remark varchar(2))

初始化資料:

INSERT INTO tb_name VALUES(1,'a');INSERT INTO tb_name VALUES(1,'b');INSERT INTO tb_name VALUES(1,'c');INSERT INTO tb_name VALUES(2,'a');INSERT INTO tb_name VALUES(2,'d');INSERT INTO tb_name VALUES(2,'e');INSERT INTO tb_name VALUES(3,'c');COMMIT;

初始資料如：

如果需要實現如下的查詢

wm_concat是oracle 10g引入，以逗號分隔串連列的值

SQL如下:

select id,wm_concat(remark) remark from tb_name t group by id

3)列轉行

 表結構:

create table TB_TEST   (     USER_NAME  VARCHAR2(20),     English   FLOAT,     Math FLOAT,     Chinese   FLOAT,  Physics FLOAT)  

初始化資料:

insert into TB_TEST (USER_NAME, ENGLISH, MATH, CHINESE, PHYSICS)values ('zhangyue', 85, 75, 97, 76);insert into TB_TEST (USER_NAME, ENGLISH, MATH, CHINESE, PHYSICS)values ('heshan', 95, 67, 79, 89);COMMIT;

初始資料如：

 

如果需要實現如下的查詢

我們這裡要使用UNION函數 請注意，UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料類型。

同時，每條 SELECT 語句中的列的順序必須相同。

SQL語句:

select user_name,'Enlish' course,ENGLISH score from tb_test t UNIONselect user_name,'Math' course,MATH score from tb_test t UNIONselect user_name,'Chinese' course,CHINESE score from tb_test t UNIONselect user_name,'Physics' course,PHYSICS score from tb_test t

4）多列轉換成字串

表結構:

create table TB_COL(  ID INTEGER,  C1 VARCHAR2(2),  C2 VARCHAR2(2),  C3 VARCHAR2(2))

初始化資料:

insert into tb_col (ID, C1, C2, C3)values (1, 'c1', 'c2', 'c3');insert into tb_col (ID, C1, C2, C3)values (2, 'c4', 'c5', 'c6');insert into tb_col (ID, C1, C2, C3)values (3, 'c7', 'c8', 'c9');COMMIT;

初始資料如：

如果需要實現如下的查詢

這個比較簡單，用||或concat函數可以實現：

SQL語句如下:

select id,c1||','||c2||','||c3 as col from tb_col t

 

以上是個簡單的小結，如果有問題請大神們告知，以便我的學習，非常感謝！