行转列:
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行列转换的例子,通过详细解释SQL语句的构成及其背后的逻辑,帮助读者理解如何在Oracle中实现行列转换。 ### Oracle行列转换例子解析 #### SQL语句结构分析 给定的SQL语句主要分为几个...
Oracle 行列转换技巧详解 Oracle 行列转换是指将数据库表中的行转换为列或将列转换为行的一种操作。这种操作在实际应用中非常有用,例如在数据报表生成、数据分析和数据挖掘等领域。下面我们将详细介绍 Oracle 行列...
通过下载提供的`Oracle补充内容_行列转换.sql`文件,你可以看到具体的SQL示例和实际操作,这将帮助你更好地理解和掌握Oracle的行列转换技巧。在实践中,理解并熟练运用这些技术,将有助于你更有效地处理和展示数据,...
在Oracle数据库中,行列转换是一项常用且强大的功能,它允许数据在不同的维度上进行转换,以便于数据分析和报告。本文将深入探讨Oracle中实现行列转换的几种方法,包括使用`UNION ALL`、`MODEL`子句以及集合类型(`...
【Oracle 行列转换实例】 在数据库管理中,有时我们需要将数据表的行与列进行转换,以便于数据分析和报表展示。Oracle 提供了一种高效的方法,即使用分析函数来实现这种行列转换。分析函数主要设计用于处理累计计算...
Oracle 行列转换总结 Oracle 行列转换是指在 Oracle 数据库中将行与列之间进行转换的操作。这种转换有六种情况:列转行、行转列、多列转换成字符串、多行转换成字符串、字符串转换成多列、字符串转换成多行。 1. ...
### Oracle 行列转换知识点详解 #### 一、概述 在数据库操作中,行列转换是一项非常实用的功能,尤其是在处理报表数据或需要特定格式输出时。本文档将详细讲解Oracle数据库中的行列转换方法,并通过具体实例来说明...
oracle行列转换示例,查询转换,有创表过程,数据,清晰明了
### Oracle 行列转换知识点详解 #### 一、概述 在数据库操作中,有时需要将数据从行格式转换为列格式或反之亦然,这种需求通常被称为“行列转换”。Oracle 提供了多种方法来实现这样的转换,适用于不同的场景。本文...
在Oracle数据库中,行列转换是一种常见的数据操作需求,主要用于改变数据的展示格式,使得数据能够更好地适应特定的分析或报告需求。本文将详细介绍如何在Oracle中实现行转列(即行列转换的一种)以及列转行的操作,...
常见的数据库面试关于行转列列转行的操作,方便你的面试,经常考到的
### Oracle 数据库中的行列转换详解 #### 一、引言 在处理复杂的数据查询与展示时,经常会遇到需要将数据库中的行数据转换成列数据的需求,这种操作通常被称为“行列转换”。例如,当我们想要汇总不同部门对各种...
行列转换,sys_connect_by_path,row_number等函数的用法
Oracle数据库中的行列转换是数据分析和报表制作中常见的需求,它涉及到将数据表中的一行数据转化为多列,或者相反,将多列数据转化成一行。在Oracle中,我们可以使用多种方法来实现这种转换,比如使用Pivot、Unpivot...
在Oracle数据库中,有时我们需要将表格中的行转换为列或将列转换为行,这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中如何进行行列转换,并提供实际的例子。 ####...
### Oracle SQLServer 行列转换知识点详解 #### 一、SQLServer 行转列方法 在SQLServer中,实现行转列的操作有两种主要方法:一种是利用`CASE...WHEN`语句配合`GROUP BY`聚合函数的方式;另一种是通过`PIVOT`操作...
在Oracle数据库中,数据的行列转换是数据处理过程中的常见操作,尤其在数据分析、报表制作以及数据展示时显得尤为重要。本篇文章将详细讲解Oracle 10g中的行列转换技术,包括如何实现行转列和列转行。 首先,我们要...