`

Oracle的行列转换

 
阅读更多
行转列:
1.列固定的情况,通过max+decode变换。
WITH t AS (
    SELECT 1 tid,'A' typeid, 'book1'  typename FROM DUAL UNION ALL
    SELECT 1 tid,'B' typeid, 'apple1' typename FROM DUAL UNION ALL
    SELECT 1 tid,'C' typeid, 'phone1' typename FROM DUAL UNION ALL
    SELECT 1 tid,'D' typeid, 'eye1'   typename FROM DUAL UNION ALL
    SELECT 2 tid,'A' typeid, 'book2'  typename FROM DUAL UNION ALL
    SELECT 2 tid,'B' typeid, 'apple2' typename FROM DUAL UNION ALL
    SELECT 2 tid,'C' typeid, 'phone2' typename FROM DUAL UNION ALL
    SELECT 3 tid,'B' typeid, 'apple3' typename FROM DUAL UNION ALL
    SELECT 3 tid,'C' typeid, 'phone3' typename FROM DUAL
)
SELECT * FROM t;

       TID TYPEID TYPENAME
---------- ------ --------
         1 A      book1
         1 B      apple1
         1 C      phone1
         1 D      eye1
         2 A      book2
         2 B      apple2
         2 C      phone2
         3 B      apple3
         3 C      phone3

SELECT t.tid,
       MAX(DECODE(t.typeid,'A',t.typename)) A,
       MAX(DECODE(t.typeid,'B',t.typename)) B,
       MAX(DECODE(t.typeid,'C',t.typename)) C,
       MAX(DECODE(t.typeid,'D',t.typename)) D
  FROM t
  GROUP BY t.tid

       TID A      B      C      D
---------- ------ ------ ------ ------
         1 book1  apple1 phone1 eye1
         2 book2  apple2 phone2 
         3        apple3 phone3 


2.列不固定的时候,通过自定义function转换。
CREATE OR REPLACE PACKAGE util IS
    TYPE CURSOR_TYPE IS REF CURSOR;
    FUNCTION ROW_TO_COL(table_name       VARCHAR2,
                        group_key        VARCHAR2,
                        col_key          VARCHAR2,
                        operation_symbol VARCHAR2,
                        calc_col         VARCHAR2,
                        order_key        VARCHAR2) RETURN CURSOR_TYPE;
END util;

CREATE OR REPLACE PACKAGE BODY util IS
    FUNCTION ROW_TO_COL(table_name       VARCHAR2,
                        group_key        VARCHAR2,
                        col_key          VARCHAR2,
                        operation_symbol VARCHAR2,
                        calc_col         VARCHAR2,
                        order_key        VARCHAR2) RETURN CURSOR_TYPE IS
        cur CURSOR_TYPE;
        TYPE arrays IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
        column_array arrays;
        strSql       VARCHAR2(500);
    BEGIN
        strSql := ' SELECT DISTINCT ' || col_key || ' FROM ' || table_name ||
                  ' ORDER BY ' || col_key;
        OPEN cur FOR strSql;
        FETCH cur BULK COLLECT
            INTO column_array;
        CLOSE cur;
    
        strSql := 'SELECT ';
        IF group_key IS NOT NULL THEN
            strSql := strSql || group_key || ',';
        END IF;
    
        FOR i IN column_array.FIRST .. column_array.LAST LOOP
            strSql := strSql || operation_symbol || '(DECODE(' || col_key ||
                      ',''' || column_array(i) || ''',' || calc_col ||
                      ',NULL)) ' || column_array(i);
            IF i < column_array.LAST THEN
                strSql := strSql || ',';
            END IF;
        END LOOP;
    
        strSql := strSql || ' FROM ' || table_name;
        IF group_key IS NOT NULL THEN
            strSql := strSql || ' GROUP BY ' || group_key;
        END IF;
    
        IF order_key IS NOT NULL THEN
            strSql := strSql || ' ORDER BY ' || order_key;
        END IF;
    
        OPEN cur FOR strSql;
        RETURN cur;
    EXCEPTION
       WHEN OTHERS THEN
          IF cur%ISOPEN THEN
              CLOSE cur;
          END IF;
          RAISE;
    END ROW_TO_COL;
END util;

--调用方式,函数返回一个游标,通过plsql/developer可以查看。
SELECT util.ROW_TO_COL('tb','tid','typeid','max','typename','tid') FROM DUAL

TID    A        B         C         D
1      book1    apple1    phone1    eye1
2      book2    apple2    phone2    
3      apple3   phone3    



3.通过层次查询,将行转换成字符串。
SELECT m.tid,
       SUBSTR(SYS_CONNECT_BY_PATH(m.typename, ','), 2) typename
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(PARTITION BY t.tid ORDER BY t.typeid) rn
          FROM t) m
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH m.rn = 1
CONNECT BY PRIOR m.rn = m.rn - 1
       AND PRIOR m.tid = m.tid;

TID TYPENAME
------- --------------------------------------------------------------------------------
1 book1,apple1,phone1,eye1
2 book2,apple2,phone2
3 apple3,phone3

Oracle 9i中没有connect_by_isleaf,可以使用分析函数实现
SELECT n.tid,
       MAX(n.typename) KEEP(DENSE_RANK LAST ORDER BY n.rn) typename
  FROM (SELECT m.tid,
               m.rn,
               SUBSTR(SYS_CONNECT_BY_PATH(m.typename, ','), 2) typename
          FROM (SELECT t.*,
                       ROW_NUMBER() OVER(PARTITION BY t.tid ORDER BY t.typeid) rn
                  FROM t) m
         START WITH m.rn = 1
        CONNECT BY PRIOR m.rn = m.rn - 1
               AND PRIOR m.tid = m.tid) n
 GROUP BY n.tid


列转行:
1.union all。
SQL> WITH t AS (
  2  SELECT '1' tid,'book1' A,'apple1' B,'phone1' C,'eye1' D FROM DUAL UNION ALL
  3  SELECT '2' tid,'book2' A,'apple2' B,'phone2' C,NULL D FROM DUAL UNION ALL
  4  SELECT '3' tid,NULL A,'apple3' B,'phone3' C,NULL D FROM DUAL
  5  )
  6  SELECT * FROM (
  7    SELECT t.tid,'A' typeid, t.a typename FROM t UNION ALL
  8    SELECT t.tid,'B' typeid, t.b typename FROM t UNION ALL
  9    SELECT t.tid,'C' typeid, t.c typename FROM t UNION ALL
 10    SELECT t.tid,'D' typeid, t.d typename FROM t
 11  ) m
 12  WHERE m.typename IS NOT NULL
 13  ORDER BY m.tid,m.typeid
 14  ;

TID TYPEID TYPENAME
--- ------ --------
1   A      book1
1   B      apple1
1   C      phone1
1   D      eye1
2   A      book2
2   B      apple2
2   C      phone2
3   B      apple3
3   C      phone3


2.字符串转列
SQL> WITH t AS (
  2  SELECT '1' tid,'book1,apple1,phone1,eye1' typename FROM DUAL UNION ALL
  3  SELECT '2' tid,'book2,apple2,phone2' typename FROM DUAL UNION ALL
  4  SELECT '3' tid,'apple3,phone3' typename FROM DUAL
  5  )
  6  SELECT tid,
  7         LEVEL AS lev,
  8         RTRIM(REGEXP_SUBSTR(typename || ',', '.*?' || ',', 1, LEVEL), ',') AS typename
  9    FROM t
 10  CONNECT BY tid = connect_by_root tid
 11         AND LEVEL <=
 12             LENGTH(REGEXP_REPLACE(typename || ',', '[^' || ',' || ']', NULL))
 13   ORDER BY 1,2;

TID        LEV TYPENAME
--- ---------- --------------------------------------------------------------------------------
1            1 book1
1            2 apple1
1            3 phone1
1            4 eye1
2            1 book2
2            2 apple2
2            3 phone2
3            1 apple3
3            2 phone3

分享到:
评论

相关推荐

    oracle行列转换例子

    本文将深入解析一个Oracle行列转换的例子,通过详细解释SQL语句的构成及其背后的逻辑,帮助读者理解如何在Oracle中实现行列转换。 ### Oracle行列转换例子解析 #### SQL语句结构分析 给定的SQL语句主要分为几个...

    oracle行列转换

    Oracle 行列转换技巧详解 Oracle 行列转换是指将数据库表中的行转换为列或将列转换为行的一种操作。这种操作在实际应用中非常有用,例如在数据报表生成、数据分析和数据挖掘等领域。下面我们将详细介绍 Oracle 行列...

    Oracle行列转换

    通过下载提供的`Oracle补充内容_行列转换.sql`文件,你可以看到具体的SQL示例和实际操作,这将帮助你更好地理解和掌握Oracle的行列转换技巧。在实践中,理解并熟练运用这些技术,将有助于你更有效地处理和展示数据,...

    oracle行列转换总结

    在Oracle数据库中,行列转换是一项常用且强大的功能,它允许数据在不同的维度上进行转换,以便于数据分析和报告。本文将深入探讨Oracle中实现行列转换的几种方法,包括使用`UNION ALL`、`MODEL`子句以及集合类型(`...

    oracle行列转换实例

    【Oracle 行列转换实例】 在数据库管理中,有时我们需要将数据表的行与列进行转换,以便于数据分析和报表展示。Oracle 提供了一种高效的方法,即使用分析函数来实现这种行列转换。分析函数主要设计用于处理累计计算...

    Oracle 行列转换 总结

    Oracle 行列转换总结 Oracle 行列转换是指在 Oracle 数据库中将行与列之间进行转换的操作。这种转换有六种情况:列转行、行转列、多列转换成字符串、多行转换成字符串、字符串转换成多列、字符串转换成多行。 1. ...

    Oracle 行列转换总结

    ### Oracle 行列转换知识点详解 #### 一、概述 在数据库操作中,行列转换是一项非常实用的功能,尤其是在处理报表数据或需要特定格式输出时。本文档将详细讲解Oracle数据库中的行列转换方法,并通过具体实例来说明...

    oracle行列转换示例

    oracle行列转换示例,查询转换,有创表过程,数据,清晰明了

    Oracle行列转换_总结

    ### Oracle 行列转换知识点详解 #### 一、概述 在数据库操作中,有时需要将数据从行格式转换为列格式或反之亦然,这种需求通常被称为“行列转换”。Oracle 提供了多种方法来实现这样的转换,适用于不同的场景。本文...

    ORACLE 行列转换

    在Oracle数据库中,行列转换是一种常见的数据操作需求,主要用于改变数据的展示格式,使得数据能够更好地适应特定的分析或报告需求。本文将详细介绍如何在Oracle中实现行转列(即行列转换的一种)以及列转行的操作,...

    oracle行列转换_面试经常考到

    常见的数据库面试关于行转列列转行的操作,方便你的面试,经常考到的

    oracle行列转换的例子

    ### Oracle 数据库中的行列转换详解 #### 一、引言 在处理复杂的数据查询与展示时,经常会遇到需要将数据库中的行数据转换成列数据的需求,这种操作通常被称为“行列转换”。例如,当我们想要汇总不同部门对各种...

    oracle sql 行列转换

    行列转换,sys_connect_by_path,row_number等函数的用法

    oracle行列转换[文].pdf

    Oracle数据库中的行列转换是数据分析和报表制作中常见的需求,它涉及到将数据表中的一行数据转化为多列,或者相反,将多列数据转化成一行。在Oracle中,我们可以使用多种方法来实现这种转换,比如使用Pivot、Unpivot...

    Oracle中SQL语句行列之间相互转换

    在Oracle数据库中,有时我们需要将表格中的行转换为列或将列转换为行,这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中如何进行行列转换,并提供实际的例子。 ####...

    Oracle SQLServer行列转换

    ### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中,实现行转列的操作有两种主要方法:一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式;另一种是通过`PIVOT`操作...

    oracle数据行列转换

    在Oracle数据库中,数据的行列转换是数据处理过程中的常见操作,尤其在数据分析、报表制作以及数据展示时显得尤为重要。本篇文章将详细讲解Oracle 10g中的行列转换技术,包括如何实现行转列和列转行。 首先,我们要...

Global site tag (gtag.js) - Google Analytics