Oracle列转行详解

一、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;