oracle 多列unpivot及unpivot 使用

最新推荐文章于 2024-08-14 11:38:38 发布
转载 最新推荐文章于 2024-08-14 11:38:38 发布 · 794 阅读 · 2
文章标签：

#数据库

本文提供了一个复杂的例子，展示了如何使用SQL创建表、插入数据，并通过puntipivot函数进行数据重组。示例涵盖了多种数据字段的处理，包括赌注、投资和责任等。SQL代码演示了如何对数据进行聚合和重新排列，以便更好地理解和分析。
这是一个复杂的例子，想对它有一个深入的使用，那仔细看看吧

点击(此处)折叠或打开

  1. create table TT_TEST2
  2. (
  3.   allup_level NUMBER(2),
  4.   cross_flag NUMBER(1),
  5.   book_id NUMBER(10),
  6.   bit_state VARCHAR2(10),
  7.   pool1_id NUMBER(10),
  8.   selection1_no VARCHAR2(10),
  9.   goal_line1 NUMBER(5,2),
  10.   pool2_id NUMBER(10),
  11.   selection2_no VARCHAR2(10),
  12.   goal_line2 NUMBER(5,2),
  13.   pool3_id NUMBER(10),
  14.   selection3_no VARCHAR2(10),
  15.   goal_line3 NUMBER(5,2),
  16.   pool4_id NUMBER(10),
  17.   selection4_no VARCHAR2(10),
  18.   goal_line4 NUMBER(5,2),
  19.   pool5_id NUMBER(10),
  20.   selection5_no VARCHAR2(10),
  21.   goal_line5 NUMBER(5,2),
  22.   pool6_id NUMBER(10),
  23.   selection6_no VARCHAR2(10),
  24.   goal_line6 NUMBER(5,2),
  25.   pool7_id NUMBER(10),
  26.   selection7_no VARCHAR2(10),
  27.   goal_line7 NUMBER(5,2),
  28.   pool8_id NUMBER(10),
  29.   selection8_no VARCHAR2(10),
  30.   goal_line8 NUMBER(5,2),
  31.   ticket_count NUMBER(10),
  32.   investment NUMBER(19),
  33.   liability NUMBER(22,2),
  34.   act_liability NUMBER(22,2)
  35. );
  36. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
  37. values (3, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 1767.74, 0);
  38. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
  39. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
  40. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
  41. values (2, 0, 0, \'0\', 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 792, 0);
  42. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
  43. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
  44. commit;
以上是基础数据，有点长，
以下是sql

点击(此处)折叠或打开

  1. WITH a AS
  2.        (SELECT allup_level,
  3.                cross_flag,
  4.                pool_id,
  5.                selection_no,
  6.                ticket_count,
  7.                investment
  8.         FROM TT_TEST2 p unpivot

  10. ((pool_id, selection_no) FOR(x1, x2) IN((pool1_id,
  11.                                                                                 selection1_no),
  12.                                                                                (pool2_id,
  13.                                                                                 selection2_no),
  14.                                                                                (pool3_id,
  15.                                                                                 selection3_no),
  16.                                                                                (pool4_id,
  17.                                                                                 selection4_no),
  18.                                                                                (pool5_id,
  19.                                                                                 selection5_no),
  20.                                                                                (pool6_id,
  21.                                                                                 selection6_no),
  22.                                                                                (pool7_id,
  23.                                                                                 selection7_no),
  24.                                                                                (pool8_id,
  25.                                                                                 selection8_no)))
  26.         WHERE pool_id > 0)
  27.       SELECT *
  28.       FROM a pivot ( SUM(decode(cross_flag, 1, ticket_count, 0)) AS tx, SUM(decode(cross_flag, 1, 0, ticket_count)) AS t, SUM(decode(cross_flag, 1, investment, 0)) AS ix, SUM(decode(cross_flag, 1, 0, investment)) AS i FOR allup_level IN(2 AS v2,
  29.                                                                                                                                                                                                                                              3 AS v3,
  30.                                                                                                                                                                                                                                              4 AS v4,
  31.                                                                                                                                                                                                                                              5 AS v5,
  32.                                                                                                                                                                                                                                              6 AS v6,
  33.                                                                                                                                                                                                                                              7 AS v7,
  34.                                                                                                                                                                                                                                              8 AS v8))
不解释，自己感受一下

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/134308/viewspace-1175926/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/134308/viewspace-1175926/

conglan7945
关注
Oracle unpivot 列转行函数问题
qq_41166869的博客
03-10 640
Oracle unpivot 函数
Oracle之列转行Unpivot函数
张大安的博客
02-25 4409
文章目录列转行Unpivot函数用法 列转行Unpivot函数用法 如每年的各项指标数据，数据格式如下： SELECT T.FC_Y AS FORECAST_YEAR, --年份 T.EPS AS IDX_EPS, -- 指标1 T.OPER_INC AS IDX_OPER_INC,-- 指标2 T.NET_PROF AS IDX_NET_PROF,-- 指标3 T.PE AS IDX_PE-- 指标4 FROM TABLE_NAME T WHERE T.ID = 1 ORDER BY T.FC
oracle中的行列转换（二 行转列 unpivot）
mix0313的博客
03-31 1062
在上次的列转行的基础之上:SELECT * FROM (SELECT deptno, empjob, sal FROM emp ) pivot( SUM(sal) FOR empjob IN ( 'PRESIDENT' AS PRESIDENT, 'MANAGER' AS MANAGER, 'ANALYST' AS ANALYST, 'CLERK' AS CLERK, 'SALESMAN'
oracle unpivot 多个,Oracle：Pivot 和 Unpivot 转多列并包含多个名称
weixin_32016817的博客
04-06 1836
Pivot1、准备数据create table t_demo(id int,name varchar(20),nums int); ---- 创建表insert into t_demo values(1, '苹果', 1000);insert into t_demo values(2, '苹果', 2000);insert into t_demo values(3, '苹果', 4000);in...
多字段unpivot
我的学习成长日记
02-19 1478
http://stackoverflow.com/questions/18617243/sql-unpivot-multiple-columns-data
Oracle 行列转换 -- pivot & unpivot
Allen Yu的博客
06-19 1236
目录 一、oracle行列转换 二、pivot & unpivot 1. pivot 1) 准备数据 2) 实现行转多列 2. unpivot 1) 准备数据 2) 实现行转列 3) 实现行转多列 三、举个栗子 1. 准备表1的数据 2. 用unpivot将数据转换为一列 3. 用pivot转换为行 4. 全部代码 一、oracle行列转换 常用的行列转换方...
oracle unpivot多列
12-08
Oracle的UNPIVOT操作可以将多列数据转换为单列数据。下面是一个例子： 假设我们有一个表，其中包含以下列：ID、NAME、SALES_Q1、SALES_Q2、SALES_Q3、SALES_Q4。我们想要将SALES_Q1、SALES_Q2、SALES_Q3和SALES_Q4...
Oracle利用PIVOT和UNPIVOT进行行列转换
个人公众号：Alen的数据库学习笔记
08-17 8252
在数据库开发中，我们会经常遇到行转列和列转行的情况，方法很多，比如使用CASE...WHEN...、DECODE，或者PIVOT和UNPIVOT都可以实现该功能，本篇讲解下PIVOT和UNPIVOT的使用，希望可以让读者举一反三，了解行列转换的用法。 PIVOT语法 SELECT ...    FROM ...    PIVOT [XML]              (
[SQL]UNPIVOT 多個欄位
weixin_30670151的博客
07-02 193
有朋友問「如何直接unpivot成2個欄位」，如下所示， 先準備測試資料如下， view source print? 01 create table T ( 02 no varchar(10), 03 yearx varchar(4), ...
unpivot转多列并包含多个名称
大龄程序员
04-22 5442
CREATE TABLE T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5),e1 NUMBER(10,5),e2 NUMBER(10,5),h1 NUMBER(10,5),h2 NUMBER(10,5)); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1,
ORACLE 中 unpivot转多列并包含多个名称
JERRY_CHEN9999
11-29 996
CREATE TABLE T5 (idnum NUMBER,f1 NUMBER(10,5),f2 NUMBER(10,5),f3 NUMBER(10,5),e1 NUMBER(10,5),e2 NUMBER(10,5),h1 NUMBER(10,5),h2 NUMBER(10,5)); INSERT INTO T5 (IDNUM,F1,F2,F3,E1,E2,H1,H2) VALUES (1...
Oracle 脚本- UNPIVOT 多列转多行
abcder12345的博客
12-04 511
行转列# #Oracle# #多列转多行#
Oracle：Pivot 和 Unpivot 转多列并包含多个名称
爱米粒的博客
08-16 1万+
Pivot 1、准备数据 create table t_demo(id int,name varchar(20),nums int); ---- 创建表 insert into t_demo values(1, '苹果', 1000); insert into t_demo values(2, '苹果', 2000); insert into t_demo values(3, ...
利用逆透视(Unpivot)将汇总列拆分成多个列
Fluffy的百宝箱
05-11 2865
利用逆透视(Unpivot)将汇总列(Delimited Column)拆分成多个列。
【Oracle】Oracle中的行转列、列转行（unpivot、pivot函数）
热门推荐
低调做人，低调编码，神码都是浮云
07-18 1万+
Oracle中的行转列和列转行
oracle unpovit 多列,Oracle 过程中执行动态 SQL 或 DDL 语句 | 隔叶黄莺 Unmi Blog
weixin_33272141的博客
04-10 310
如果你用的是 Oracle 8i 及以上的版本，那简单，在过程中用 execute immediate sql_str就行, sql_str 是一个拼凑的 SQL 语句，但这个动态语句中带参数，或 Select 的结果要 into 到变量中时就要稍加留心一下了。而在 8i 以前的版本(谁还用这么古老的玩艺，总有些不得已的地方，老系统考虑升级成本遗留下来的，应用软件所伴随着的等)，都没法用 exe...
oracle UNPIVOT的使用
最新发布
weixin_41203765的博客
08-14 1137
Oracle UNPIVOT是一种用于将列转换为行的SQL操作，它允许用户将多个列的数据转换为多行的形式，以便进行更灵活的数据分析和报表生成。
ORACLE UNPIVOT函数
RogerFedererGO的博客
07-26 3555
ORACLE UNPIVOT函数 ORA-01790:表达示必须具有与对应表达式相同的数据类型
oracle unpivot用法
学渣的博客
07-13 2153
Oracle UNPIVOT函数是将列旋转为行的一种方式。UNPIVOT函数可以将多个列转换为对应的行，以便进行聚合或插入操作。UNPIVOT函数可以将多个列转换为对应的行，以便进行聚合或插入操作。在这个查询中，首先从表中选择所有列，然后使用UNPIVOT函数将Name，Age和Gender列转换为对应的行。最终结果将为每个ID和属性组合返回一个行。其中属性是列标题（Name，Age，Gender），值是对应的单元格值。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值