ORACLE分析函数(6) 使用listagg实现行列转换

listagg函数是在oracle11g中新引入的聚合函数，当然也可以作为分析函数来使用。该函数可以基于分组内特定列的排序，将测量列进行拼接合并。

示例：

在10g中，该函数不被支持

SQL> select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
  2  where sales.cust_id = customers.cust_id and cust_gender = 'M'
and cust_credit_limit = 15000 and prod_id between 15 and 18
and channel_id =2 and time_id > to_date('01-06-01','YY-MM-DD')
group by prod_id;  3    4    5
select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
                                                                         *
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

在11g中，运行结果如下：

SQL> l
  1  select prod_id, LISTAGG(cust_first_name||' '||cust_last_name,';') WITHIN GROUP (order by amount_sold) cust_name from sales, customers
  2  where sales.cust_id = customers.cust_id and cust_gender = 'M'
  3  and cust_credit_limit = 15000 and prod_id between 15 and 18
  4  and channel_id =2 and time_id > to_date('01-06-01','YY-MM-DD')
  5* group by prod_id
SQL> /  

   PROD_ID CUST_NAME
---------- ----------------------------------------------------------------------------------------------------
    15 Mason Murray;Helga Nickols;Roxanne Crocker;Glenn Wong;Roxanne Crocker;Franklin Hagan;Franklin Hagan;
       Ransom Wiser;Reyburn Markman  

    16 Forrest Lindsey;Helga Nickols;Helga Nickols;Roxanne Crocker;Garrett Manson;Roxanne Crocker;Franklin
       Hagan;Prane Oppy;Franklin Hagan;Bud Smyth;Reyburn Markman  

    17 Garrett Manson;Roxanne Crocker;Helga Nickols;Roxanne Crocker;Helga Nickols;Mason Murray;Ethan Jeffre
       ys;Franklin Hagan;Prane Oppy;Royd Ricketts  

    18 Erik Ready;Garrett Manson;Forrest Lindsey;Franklin Hagan;Franklin Hagan;Reyburn Markman

当然，listagg也可以基于over()的分区。

示例：

SQL> l
  1  select time_id,prod_id,min(amount_sold),listagg(min(amount_sold),';') within group (order by prod_id) over (partition by time_id) cust_list
  2  from sales where time_id > to_date('20-DEC-01','DD-MON-YY','NLS_DATE_LANGUAGE = American') and prod_id between 120 and 125
  3* group by prod_id,time_id
SQL> /  

TIME_ID            PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-21 00:00:00    120        51.36 51.36;10.81
2001-12-21 00:00:00    121        10.81 51.36;10.81
2001-12-22 00:00:00    120        51.36 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00    121        10.81 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00    122        20.23 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00    123        56.12 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00    124        17.79 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-22 00:00:00    125        15.67 51.36;10.81;20.23;56.12;17.79;15.67
2001-12-23 00:00:00    120        51.36 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00    121        10.49 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00    122        20.23 51.36;10.49;20.23;57.86;17.79;15.67  

TIME_ID            PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-23 00:00:00    123        57.86 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00    124        17.79 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-23 00:00:00    125        15.67 51.36;10.49;20.23;57.86;17.79;15.67
2001-12-24 00:00:00    120        51.36 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00    121        10.49 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00    122        20.23 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00    123        56.12 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00    124        17.79 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-24 00:00:00    125        15.67 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-25 00:00:00    120        51.36 51.36;10.81
2001-12-25 00:00:00    121        10.81 51.36;10.81  

TIME_ID            PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-26 00:00:00    123        57.86 57.86
2001-12-27 00:00:00    120        51.36 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00    121        10.49 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00    122        20.23 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00    123        56.12 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00    124        17.79 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-27 00:00:00    125        15.67 51.36;10.49;20.23;56.12;17.79;15.67
2001-12-28 00:00:00    120        51.36 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00    121        10.49 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00    123        56.12 51.36;10.49;56.12;17.79;15.67
2001-12-28 00:00:00    124        17.79 51.36;10.49;56.12;17.79;15.67  

TIME_ID            PROD_ID MIN(AMOUNT_SOLD) CUST_LIST
------------------- ---------- ---------------- ----------------------------------------------------------------------
2001-12-28 00:00:00    125        15.67 51.36;10.49;56.12;17.79;15.67
2001-12-29 00:00:00    122        20.23 20.23;56.12;17.79;15.67
2001-12-29 00:00:00    123        56.12 20.23;56.12;17.79;15.67
2001-12-29 00:00:00    124        17.79 20.23;56.12;17.79;15.67
2001-12-29 00:00:00    125        15.67 20.23;56.12;17.79;15.67
2001-12-30 00:00:00    120        51.36 51.36;10.49
2001-12-30 00:00:00    121        10.49 51.36;10.49
2001-12-31 00:00:00    121        10.81 10.81  

已选择41行。

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索12
00
oracle 行列转换、oracle 行列转换函数、oracle中行列转换、oracle行列转换 pivot、oracle 动态行列转换，以便于您获取更多的相关知识。

时间： 2024-10-28 05:12:06

ORACLE分析函数(6) 使用listagg实现行列转换的相关文章

Oracle技术：使用分析函数进行行列转换

经常有朋友问到行列转换的问题,留言板上也有这样的提问. 其实使用分析函数进行处理是很好的方式,翻一下Tom的书,将其中的一个例子收录在这里. 比如查询scott.emp表的用户SAL排序信息,可以使用如下查询: SQL> SELECT deptno, ename, 2 ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq 3 FROM emp; DEPTNO ENAME SEQ

Oracle实现行列转换的方法分析_oracle

本文实例讲述了Oracle实现行列转换的方法.分享给大家供大家参考,具体如下: 1.固定列数的行列转换如: student subject grade --------- ---------- -------- student1 语文 80 student1 数学 70 student1 英语 60 student2 语文 90 student2 数学 80 student2 英语 100 -- 转换为: 语文数学英语 student1 80 70 60 student2 90 80 10

Oracle的行列转换

行列转换包括以下六种情况: 1. 列转行 2. 行转列 3. 多列转换成字符串 4. 多行转换成字符串 5. 字符串转换成多列 6. 字符串转换成多行首先声明,有些例子需要如下10g及以后才有的知识: A. 掌握model子句, B. 正则表达式 C. 加强的层次查询 1.列转行 CREATE TABLE t_col_row( ID INT, c1 VARCHAR2(10), c2 VARCHAR2(10), c3 VARCHAR2(10)): INSERT INTO t_col_row VA

使用分析函数进行行列转换

其实使用分析函数进行处理是很好的方式,翻一下Tom的书,将其中的一个例子收录在这里. 比如查询scott.emp表的用户SAL排序信息,可以使用如下查询: SQL> SELECT deptno, ename, 2 ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq 3 FROM emp; DEPTNO ENAME SEQ ---------- ---------- ---------- 10 KING 1 10 CLARK

[数据库] SQL查询语句表行列转换及一行数据转换成两列

本文主要讲述了SQL查询语句表之间的行列转换,同时也包括如何将一行数据转换成两列数据的方法.子查询的应用.decode函数的用法.希望文章对你有所帮助~ 1.创建数据库表及插入数据 2.子查询统计不同性质的学生总数 3.一行数据转换成两列数据 union all 4.表行列数据转换(表转置) 1.创建数据库表及插入数据创建数据库.创建学生表并设置主键.插入数据代码如下: --创建数据库 create database StudentMS --使用数据库 use StudentMS --创建学生

03. 行列转换写法小结

原文:03. 行列转换写法小结行列转换,通常有2种方法,一是CASE WHEN/UNION:一是PIVOT/UNPIVOT.对于行值或列数不固定的情况,需要用动态SQL. 一. 行转列 --drop table RowToCol create table RowToCol ( ID int, Code varchar(10), Value int ) Go insert RowToCol select 1,'Item1',1000 union all select 1,'Item2',1000

Oracle CBO几种基本的查询转换详解

Oracle CBO几种基本的查询转换详解查询转换(Query Transformation),又称为逻辑优化(Logical Optimization),也称为软优化,即查询转换器在逻辑上对语句做一些语义等价转换,从而能使优化器生成效率更高的执行计划. 语句在被提交到Oracle后,解析器(Parser)会对SQL语句的语法.语义进行分析,并将查询中的视图展开.划分为小的查询块(Query Block).它们是嵌套或者相互关联的,而查询形式则决定了它们之间是如何关联的.这些查询块被传送给了查

行列转换

转换 1. 行列转换--普通假设有张学生成绩表(CJ)如下 [姓名] [学科] [成绩]张三语文 80 张三数学 90 张三物理 85 李四语文 85 李四数学 92 李四物理 82王五数学 60 想变成 [姓名] [语文] [数学] [物理]张三 80 90 85 李四 85 92 82王五 null 60 null decla

在WPS中用转置实现表格行列转换

有时,我们需要将已经做好的表格的行.列对调一下,手工转换不仅费时费力,还容易出错,那有没有更简单也更可靠的方式实现表格中的行列转换呢? 方法一:用WPS表格的"选择性粘贴"实现转换. 在WPS表格2005中,选中整个表格,复制到剪贴版,然后将光标置于空白处,从菜单中点选"编辑-选择性粘贴",在弹出的对话框中选中"转置"(图1),确定后即把整个表格"翻了个个儿"粘贴到指定位置了(图2).如果要转换的是WPS文字中的表格,可以先在