Oracle PIVOT (二)实列

最新推荐文章于 2022-12-12 15:29:54 发布
原创 最新推荐文章于 2022-12-12 15:29:54 发布 · 1.2k 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#Oracle PIVOT

本文详细介绍了Oracle数据库中Pivot函数的使用方法，包括如何实现行转列操作，以及通过实例展示了Pivot函数在数据汇总和分析中的具体应用。同时，还提供了使用Max函数结合Decode或Case When进行行转列的替代方案。

 

--使用格式：

select t2.* from table1 as t1
pivot ([聚合函数] for [行转列的条件列] in ([与“行转列条件列”匹配]) t2;

/*
聚合函数（sum，max，avg等），可以支持一或多个，但是需要使用别名（列：sum(t1.x1) a,sum(t1.x2) b)
行转列的条件列，如果是需要多个列，使用concat或者||拼接成一个列
*/

 

with tab1 as (
select 1 BH, '单选' C_TX, 1   N_FS , 5 N_TS from dual union all
select 1 BH, '多选' C_TX, 1   N_FS , 2 N_TS from dual union all
select 2 BH, '单选' C_TX, 0.5 N_FS , 4 N_FS from dual union all
select 2 BH, '多选' C_TX, 1   N_FS , 3 N_FS from dual 
)
select *
  from (select BH, C_TX,N_FS,N_TS
  from tab1)pivot(MAX(N_FS) 每题分数,MAX(N_TS) 分配题数 for C_TX in ('单选' as 单选,
                              '多选' AS 多选,
                              '判断' AS 判断))
;
/*
--结果
BH	单选_每题分数	单选_分配题数	多选_每题分数	多选_分配题数	判断_每题分数	判断_分配题数
1	1	            5	            1	            2		
2	0.5	            4	            1	            3
*/

实例：

/*源数据*/
SELECT *
  FROM (SELECT A1.C_ZJGZMXJLBH, A1.N_BZFZ, A2.C_TX, A2.N_FS, A2.N_TS
          FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
         WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) T1 PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4)) ;


/*
C_ZJGZMXJLBH	N_BZFZ	N_FZHJ	N_STLX_1_FS	N_STLX_1_TS	N_STLX_2_FS	N_STLX_2_TS	N_STLX_3_FS	N_STLX_3_TS	N_STLX_4_FS	N_STLX_4_TS
3		8.00	0								
1		10.00	7	1		5		1		2				
2		5.00	5	0.5		4		1		3				
*/
--方式一
SELECT T1.C_ZJGZMXJLBH ,T1.N_BZFZ,
       NVL(T1.N_STLX_1_FS * T1.N_STLX_1_TS,0) +
       NVL(T1.N_STLX_2_FS * T1.N_STLX_2_TS,0) +
       NVL(T1.N_STLX_3_FS * T1.N_STLX_3_TS,0) +
       NVL(T1.N_STLX_4_FS * T1.N_STLX_4_TS,0)  N_FZHJ,
       T1.N_STLX_1_FS, T1.N_STLX_1_TS ,
       T1.N_STLX_2_FS, T1.N_STLX_2_TS ,
       T1.N_STLX_3_FS, T1.N_STLX_3_TS ,
       T1.N_STLX_4_FS, T1.N_STLX_4_TS 
  FROM(
  SELECT *
  FROM (SELECT A1.C_ZJGZMXJLBH, A1.N_BZFZ, A2.C_TX, A2.N_FS, A2.N_TS
          FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
         WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4)) 
  ) T1;
/*
C_ZJGZMXJLBH	N_FZHJ	N_STLX_1_FS	N_STLX_1_TS	N_STLX_2_FS	N_STLX_2_TS	N_STLX_3_FS	N_STLX_3_TS	N_STLX_4_FS	N_STLX_4_TS
1		7		1	5		1		2		0	0	0	0
3		0		0	0		0		0		0	0	0	0
2		5		0.5	4		1		3		0	0	0	0
*/
--方式二 
SELECT T1.C_ZJGZMXJLBH ,SUM(NVL(T1.N_FZHJ,0)) N_FZHJ,
       SUM(NVL(T1.N_STLX_1_FS,0)) N_STLX_1_FS, SUM(NVL(T1.N_STLX_1_TS,0)) N_STLX_1_TS,
       SUM(NVL(T1.N_STLX_2_FS,0)) N_STLX_2_FS, SUM(NVL(T1.N_STLX_2_TS,0)) N_STLX_2_TS,
       SUM(NVL(T1.N_STLX_3_FS,0)) N_STLX_3_FS, SUM(NVL(T1.N_STLX_3_TS,0)) N_STLX_3_TS,
       SUM(NVL(T1.N_STLX_4_FS,0)) N_STLX_4_FS, SUM(NVL(T1.N_STLX_4_TS,0)) N_STLX_4_TS
  FROM(
  SELECT *
    FROM (SELECT A1.C_ZJGZMXJLBH,  A2.C_TX, A2.N_FS, A2.N_TS,A2.N_FS* A2.N_TS N_FZHJ
            FROM T_KT_ZJGZ_MX A1, T_KT_ZJGZ_TX A2
           WHERE A1.C_ZJGZMXJLBH = A2.C_ZJGZMXJLBH(+)) T1 PIVOT(SUM(N_FS) FS, SUM(N_TS) TS FOR C_TX IN ('1' AS N_STLX_1, '2' AS N_STLX_2, '3' AS N_STLX_3, '4' AS N_STLX_4))
  ) T1
  group by T1.C_ZJGZMXJLBH;

 

 

参考 https://blog.youkuaiyun.com/paopaopotter/article/details/81735922

参考：https://www.cnblogs.com/xiao02fang/p/9705609.html

工作中，我们经常会碰到行转列的情况

这里我介绍几种简单的方法--行转列

1.oracle的pivot函数

原表

使用pivot函数：

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all 
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all 
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all 
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all 
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all 
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all 
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual 
)
select * from （select nation,city,ranking from temp）pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三,'第四' AS 第四));

说明：pivot（聚合函数 for 列名 in（类型）），其中 in(‘’) 中可以指定别名，in中还可以指定子查询，比如 selectdistinct ranking from temp

 

SELECT * FROM [StudentScores] /*数据源*/
AS P
PIVOT 
(
    SUM(Score/*行转列后 列的值*/) FOR 
    p.Subject/*需要行转列的列*/ IN ([语文],[数学],[英语],[生物]/*列的值*/)
) AS T

2.使用max结合decode函数

原表

使用max结合decode函数

with temp as(
select '四川省' nation ,'成都市' city,'第一' ranking from dual union all 
select '四川省' nation ,'绵阳市' city,'第二' ranking from dual union all 
select '四川省' nation ,'德阳市' city,'第三' ranking from dual union all 
select '四川省' nation ,'宜宾市' city,'第四' ranking from dual union all 
select '湖北省' nation ,'武汉市' city,'第一' ranking from dual union all 
select '湖北省' nation ,'宜昌市' city,'第二' ranking from dual union all 
select '湖北省' nation ,'襄阳市' city,'第三' ranking from dual 
)
select nation,
max(decode(ranking, '第一', city, '')) as 第一,
max(decode(ranking, '第二', city, '')) as 第二,
max(decode(ranking, '第三', city, '')) as 第三,
max(decode(ranking, '第四', city, '')) as 第四
from temp group by nation;

说明：decode的用法：decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

3.使用max结合case when 函数

原表

使用max结合case when 函数

select case when grade_id='1' then '一年级'

when grade_id='2' then '二年级'

when grade_id='5' then '五年级' 

else null end "年级",

max(case when subject_name='语文'  then max_score

else 0 end) "语文" ,

max(case when subject_name='数学'  then max_score

else 0 end) "数学" ,

max(case when subject_name='政治'  then max_score

else 0 end) "政治"

from dim_ia_test_ysf

group by

case when grade_id='1' then '一年级'

when grade_id='2' then '二年级'

when grade_id='5' then '五年级' 

else null end

 

 

 

下面是介绍列转行方法--列转行

原表

with temp as(
select '四川省' nation ,'成都市' 第一,'绵阳市' 第二,'德阳市' 第三,'宜宾市' 第四  from dual union all 
select '湖北省' nation ,'武汉市' 第一,'宜昌市' 第二,'襄阳市' 第三,'' 第四   from dual 
)
select nation,name,title from 
temp
unpivot
(name for title in (第一,第二,第三,第四))t

 说明：unpivot（自定义列名/*列的值*/ for 自定义列名/*列名*/ in（列名））

PIVOT函数-动态列
qq_31532983的博客
06-04 1705
将pm_name的值转成对应的列，效果如下。
Oracle 行列转换函数pivot使用
JasonStanZ的博客
11-03 1962
问题描述： 描述：在项目中，需要将表中日期数据行，用视图转列显示月报表 解决方案： 注意：多聚合必须重命名。 create table pivottest( 主键 varchar2(50) default sys_guid() not null, 日期 varchar2(10), 学生姓名 varchar2(20), 学生分组 varchar2(10), 学号 varchar2(10) ) insert into pivottest (主键,日期,学生姓名,学生分组,学号) values (
PIVOT函数，行转列
Ken的博客
09-14 2966
  PIVOT函数的格式如下  PIVOT(<聚合函数>([聚合列值]) FOR [行转列前的列名] IN([行转列后的列名1],[行转列后的列名2],[行转列后的列名3] CREATE TABLE #Temp (TypeClass NVARCHAR(200),  Name NVARCHAR(200),  Price int) INSERT INTO #Temp (TypeClass...
oracle pivot/unpivot 查询
Uncle.Cui的技术博客
06-03 1526
oracle pivot unpivot 使用pivot之后的写法更加优雅，如下： pivot 的语法如下： unpivot的用法： 语法示例： SQL查询示例：
oracle pivot 不能用子查询,Oracle pivot in子查询的疑问
weixin_31244759的博客
04-07 2153
按照oracle的文档，pivot语句中in后面的列如果不固定，只能使用xml格式的返回结果，如：http://www.oracle-developer.n...SQL> SELECT *2 FROM pivot_data3 PIVOT XML4 (SUM(sal) FOR deptno IN (ANY));JOB DEPTNO_XML--------- ...
Oracle行转列之pivot
03-15
从Oracle 11g版本开始，引入了PIVOT和UNPIVOT关键字，以支持显式的查询转换，即从行数据转换为列数据，或从列数据转换为行数据，进而生成交叉表格式的结果。这一技术尤其适用于报表生成，使得能够使用SQL语句针对...
oracle pivot unpivot 行列互换
m0_60311330的博客
10-04 568
1、工作问题 最近工作上遇到一个数学问题：校验五个（或以上）数字不能重复出现两次或以上。 数据表如下形式： create table test001 ( id_test varchar2(10), col_1 int, col_2 int, col_3 int, col_4 int, col_5 int ); insert into test001(id_test, col_1, col_2, col_3, col_4, col_5) values('a',1,2,3,4,5);
Oracle 行转列、列转行详解（pivot、unpivot）
鱼丸丶粗面
04-20 1万+
文章目录1 概述2 示例2.1 pivot：行转列2.2 unpivot：列转行 1 概述 #mermaid-svg-H1fXJe7PJfunNktj {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-H1fXJe7PJfunNktj .error-icon{fill:#552222;}#mermaid-svg-H1fXJe7PJfunNktj .error-text{fil
Oracle 行转列 pivot函数基本用法
瓶子里的小地盘
01-01 1491
一、运行环境 目前该语法使用Oracle11g+版本,首先请核实你的数据库版本是否支持该语法; select * from v$version; 二、看下结果 三、测试数据准备 --建表 --drop table SalesList; create table SalesList( keHu varchar2(20), --客户 ...
【ORACLE】个人开发PIVOT增强包-解决原生PIVOT在不使用XML时无法in子查询的问题
DarkAthena的博客
12-25 2697
pivot_ext_pkg-oracle 项目地址: https://github.com/Dark-Athena/pivot_ext_pkg-oracle 背景 众所周知,oracle的原生pivot功能,无法在"for ... in ()" 的括号中使用子查询(不使用xml的情况下), 比如,如果执行 sql select * from HR.COUNTRIES pivot(MAX(COUNTRY_NAME) for COUNTRY_ID in (select distinct COUNTRY
Oracle——子查询
weixin_30414245的博客
05-06 130
子查询 不能一步求解的时候使用子查询 单行子查询 单行操作符 示例：查询工资比SCOTT高的员工信息 SELECT * FROM emp WHERE sal > ( SELECT sal FROM emp WHERE ename = 'SCOTT' ); 注意点 1、括号 2、合理的书写风格 3、可以在主查询的where select h...
oracle行转列函数pivot
咖啡杯里的茶
04-16 3312
什么情形下需要使用行转列 对于同一类的事物下具有多个属性(属性是有限的)，比如说学生选课，每一名可以选择多个课程，而且课程的数量也是有限重复的(对于不通学生选同一门科就相当于是课程重复)。比如说下面这样的数据： 姓名 班级 性别 课程 分数 张三 一班 男 高等数学 96 张三 一班 男 复变函数 78 张三 一班 男 英语 78 李四 二班 男 英语 98 李四 ...
oracle非常量不能用于privot_Oracle pivot in子查询的疑问
weixin_39860201的博客
01-13 499
按照oracle的文档，pivot语句中in后面的列如果不固定，只能使用xml格式的返回结果，如：http://www.oracle-developer.n...SQL> SELECT *2 FROM pivot_data3 PIVOT XML4 (SUM(sal) FOR deptno IN (ANY));JOB DEPTNO_XML--------- ...
sql按两列pivot_如何使用SQL PIVOT比较数据库中的两个表
danpu0978的博客
05-17 1030
sql按两列pivot 这很容易发生。 您可以通过添加新列来调整表： ALTER TABLE payments ADD code NUMBER(3); 您继续执行您的业务逻辑–绝对没有问题。 但是，随后（也许在生产中）某些批处理作业失败，因为它对数据类型做出了一些强有力的假设。 即，它假定两个表payments和payments_archive具有相同的行类型： CREATE TABLE ...
Oracle中pivot函数详解
热门推荐
实泽有之，无泽虚之
12-12 3万+
pivot函数格式：pivot(聚合函数 for 需要转为列的字段名 in(需要转为列的字段值))； pivot函数说明：实现将指定字段的值转换为列的效果。
for oracle中pivot_Oracle中使用pivot的问题
weixin_32540109的博客
12-23 248
展开全部pivot这个是sqlserver的函数啊，oracle中没有的。用这个吧：selectdiv_code,sum(casewhenpayout_kind_name='物价生活性32313133353236313431303231363533e4b893e5b19e31333433663036补贴’thenmoneyelse0end)物价生活性补贴,sum(cas...
oralce函数 PIVOT (聚合函数（列）for in（）) 行转列
爱笑才不是傻帽的博客
02-27 4939
PIVOT 语法为 SELECT <非透视的列>, [第一个透视的列],...[最后一个透视的列] FROM (<生成数据的 SELECT 查询>) PIVOT (<聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列],... [最后一个透视的列]) ) AS <透视表的别名&gt...
for oracle中pivot_如何在Oracle中PIVOT结果？
weixin_30539317的博客
02-06 284
问题：您要在Oracle中PIVOT结果。解PIVOT子句是Oracle Database 11g的新增功能，它使您可以将行翻转为查询输出中的列，同时，还可以对数据运行聚合函数。PIVOT对于查看大量数据的总体趋势特别有用。我们将使用销售数据来演示用法。示例SELECT*FROMsales;输出结果12008121998-02-286375521898.881200...
Oracle pivot
最新发布
07-04
### Oracle PIVOT 行转列使用方法示例 Oracle 数据库中的 `PIVOT` 操作用于将行数据转换为列数据，适用于需要对数据进行横向展示的场景。该功能在 Oracle 11g 及以上版本中引入，可以简化传统通过 `DECODE` 或 `CASE WHEN` 实现的复杂 SQL 查询。 #### 基本语法结构 ```sql SELECT * FROM ( -- 子查询，提供基础数据 ) PIVOT ( -- 聚合函数，例如 SUM、MAX 等 FOR 列名 IN (值1 AS 别名1, 值2 AS 别名2, ...) ); ``` #### 示例一：静态列定义 假设有一个表 `SALES_DATA`，记录了不同产品在不同年份的销售金额： | PRODUCT | YEAR | AMOUNT | |---------|------|--------| | A | 2020 | 100 | | A | 2021 | 150 | | B | 2020 | 200 | | B | 2021 | 250 | 要将 `YEAR` 字段的每个值作为列展示，并显示对应的 `AMOUNT`： ```sql SELECT * FROM ( SELECT PRODUCT, YEAR, AMOUNT FROM SALES_DATA ) PIVOT ( SUM(AMOUNT) FOR YEAR IN (2020 AS Y2020, 2021 AS Y2021) ); ``` 执行结果如下： | PRODUCT | Y2020 | Y2021 | |---------|-------|-------| | A | 100 | 150 | | B | 200 | 250 | 此方法适用于列的数量和名称已知的情况[^3]。 #### 示例二：动态列生成（PL/SQL） 当列的数量不确定时，可以通过 PL/SQL 动态构建 SQL 语句。以下是一个简单的动态 `PIVOT` 示例： ```plsql DECLARE v_sql VARCHAR2(4000); v_columns VARCHAR2(4000); BEGIN -- 获取所有可能的列名并拼接成字符串 SELECT LISTAGG('''' || YEAR || ''' AS Y' || YEAR, ', ') WITHIN GROUP (ORDER BY YEAR) INTO v_columns FROM (SELECT DISTINCT YEAR FROM SALES_DATA); -- 构建完整的动态 SQL v_sql := 'SELECT PRODUCT, ' || v_columns || ' FROM ( SELECT PRODUCT, YEAR, AMOUNT FROM SALES_DATA ) PIVOT ( SUM(AMOUNT) FOR YEAR IN (' || v_columns || ') )'; -- 执行动态 SQL EXECUTE IMMEDIATE v_sql; END; ``` 上述代码首先通过 `LISTAGG` 函数动态获取所有 `YEAR` 值并拼接成列名，然后将其插入到 `PIVOT` 查询中，最终通过 `EXECUTE IMMEDIATE` 执行动态生成的 SQL 语句[^2]。 #### 示例三：结合 `DECODE` 进行布尔判断 在某些情况下，除了行转列外，还需要根据列值是否出现返回布尔值。例如，在一个客户类型表中，每条记录代表客户的一个类型，目标是将每个类型横向展开，并标记是否存在： ```sql SELECT CUSTOMER_ID, DECODE(C1, 1, 'true', 'false') AS C1, DECODE(C2, 2, 'true', 'false') AS C2, DECODE(C3, 3, 'true', 'false') AS C3 FROM ( SELECT CUSTOMER_ID, TYPE_CD FROM CUSTOMER_TYPES ) PIVOT ( COUNT(TYPE_CD) FOR TYPE_CD IN (1 AS C1, 2 AS C2, 3 AS C3) ); ``` 此查询中，`COUNT(TYPE_CD)` 用于判断某个类型是否存在，若存在则计数大于 0，否则为 0。再通过 `DECODE` 将其转换为 `'true'` 或 `'false'`，便于前端处理[^3]。 #### 总结 - **静态列**：适用于列数量和名称已知的场景，直接在 `PIVOT` 中指定。 - **动态列**：通过 PL/SQL 动态生成 SQL，适用于列数量未知或经常变化的情况。 - **结合 `DECODE`**：用于将数值型标志转换为布尔值，提升数据可读性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值