一、Oracle列轉行函數
Oracle列轉行函數主要用於把一列的數據轉換成行,MySQL也有同樣的功能,不過不是用函數實現,而是指定一個參數。
Oracle列轉行函數需要按照以下步驟進行操作:
CREATE OR REPLACE FUNCTION col2row(tabname VARCHAR2,colname VARCHAR2)
RETURN VARCHAR2 DETERMINISTIC
IS
colhead VARCHAR2(500) := NULL;
rowbody VARCHAR2(500) := NULL;
BEGIN
--獲取列名
SELECT col INTO colhead FROM t_col2row WHERE tab_name = tabname;
IF colhead IS NULL THEN
RAISE_APPLICATION_ERROR(-20001,'the input table does not exist or the input column does not exist!');
END IF;
--拼接行
FOR r IN (SELECT colhead||'='||colname FROM tabname) LOOP
rowbody := rowbody||','||r.colname;
END LOOP;
RETURN LTRIM(rowbody,',');
END;
二、Oracle列轉行decode
Oracle列轉行decode是一種常用的列轉行方式,利用DECODE函數實現列轉行。DECODE函數的語法為:DECODE(expr,search,result,default)。search可以是一個具體值,也可以是一個條件,而result是在search匹配到expr時需要返回的結果。default是可選項,如果所有條件都不滿足,返回default。
使用DECODE實現列轉行的代碼示例如下:
SELECT NAME,
DECODE(ID,1,SCORE) AS ALGEBRA,
DECODE(ID,2,SCORE) AS ENGLISH,
DECODE(ID,3,SCORE) AS MATHS
FROM t_score
WHERE NAME = 'Tom';
三、Oracle列轉行函數wm
Oracle列轉行函數wm是一種比較常用的列轉行方式,利用WM_CONCAT函數實現列轉行。WM_CONCAT函數可以把同一列的多行數據合併成一行,數據之間可以使用指定的連接符隔開,如果不指定連接符,則使用逗號作為默認連接符。
下面是使用WM_CONCAT函數實現列轉行的代碼示例:
SELECT NAME,
WM_CONCAT(DECODE(ID,1,SCORE)) AS ALGEBRA,
WM_CONCAT(DECODE(ID,2,SCORE)) AS ENGLISH,
WM_CONCAT(DECODE(ID,3,SCORE)) AS MATHS
FROM t_score
WHERE NAME = 'Tom'
GROUP BY NAME;
四、Oracle列轉行教程
進行Oracle列轉行操作時,可以使用Pivot、unpivot、case when形式、row_number over ()分組、connect by生成序列等多種方式,這裡只介紹一種基礎的方式——UNPIVOT
SELECT *
FROM (SELECT NAME,A,B,C
FROM t_grade
WHERE NAME = 'Jerry')
UNPIVOT
(SCORE FOR SUBJECT IN (A,B,C));
五、Oracle列轉行sql
Oracle列轉行操作時,需要構造出特定的SQL語句,可以使用子查詢、自連接、union、pivot、unpivot等語法。
以下是利用子查詢的方式實現Oracle列轉行的代碼示例:
SELECT NAME,
(SELECT SCORE
FROM t_score
WHERE NAME = a.NAME AND ID = 1) AS ALGEBRA,
(SELECT SCORE
FROM t_score
WHERE NAME = a.NAME AND ID = 2) AS ENGLISH,
(SELECT SCORE
FROM t_score
WHERE NAME = a.NAME AND ID = 3) AS MATHS
FROM (SELECT DISTINCT NAME FROM t_score) a;
六、Oracle列轉行拼接 高效
Oracle列轉行拼接是一種比較高效的列轉行方式,可以利用SYS_CONNECT_BY_PATH函數實現。該函數可以把同一組數據中的每個值拼接成一個字元串,使用指定連接符隔開。
SELECT NAME,
LTRIM(SYS_CONNECT_BY_PATH(DECODE(ID,1,SCORE,NULL),',')||',',
',') AS ALGEBRA,
LTRIM(SYS_CONNECT_BY_PATH(DECODE(ID,2,SCORE,NULL),',')||',',
',') AS ENGLISH,
LTRIM(SYS_CONNECT_BY_PATH(DECODE(ID,3,SCORE,NULL),',')||',',
',') AS MATHS
FROM t_score
WHERE NAME = 'Tom'
START WITH ID = 1
CONNECT BY PRIOR ID = ID-1
AND NAME = NAME;
七、Oracle列轉行逗號隔開
Oracle列轉行逗號隔開可以使用LISTAGG函數實現。該函數可以把同一組數據中的每個值拼接成一個字元串,使用指定連接符隔開。
SELECT NAME,
LISTAGG(DECODE(ID,1,SCORE,NULL),',') WITHIN GROUP (ORDER BY NAME) AS ALGEBRA,
LISTAGG(DECODE(ID,2,SCORE,NULL),',') WITHIN GROUP (ORDER BY NAME) AS ENGLISH,
LISTAGG(DECODE(ID,3,SCORE,NULL),',') WITHIN GROUP (ORDER BY NAME) AS MATHS
FROM t_score
WHERE NAME = 'Tom'
GROUP BY NAME;
八、Oracle列轉行pivot
Oracle列轉行pivot在Oracle 11g之後才支持,可以通過聚合函數來將行轉換成列。
SELECT *
FROM t_score
PIVOT (SUM(SCORE)
FOR (ID)
IN (1 AS ALGEBRA,2 AS ENGLISH,3 AS MATHS));
九、Oracle列轉行函數怎麼用
Oracle列轉行函數可以按照以下步驟使用:
1、創建表格存放需要轉換的數據
CREATE TABLE t_score
(
NAME VARCHAR2(50),
ID NUMBER,
SCORE NUMBER
);
2、向表格中插入需要轉換的數據
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Tom',1,90);
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Tom',2,80);
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Tom',3,70);
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Jerry',1,60);
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Jerry',2,70);
INSERT INTO t_score (NAME,ID,SCORE) VALUES ('Jerry',3,80);
3、使用列轉行函數進行轉換
SELECT NAME,
col2row('T_SCORE','SCORE') AS SCORE
FROM t_score;
原創文章,作者:小藍,如若轉載,請註明出處:https://www.506064.com/zh-tw/n/158926.html