Oracle PIVOT (二)实列

最新推荐文章于 2022-12-12 15:29:54 发布
最新推荐文章于 2022-12-12 15:29:54 发布
阅读量1.1k 收藏 3
点赞数
分类专栏： oracle PIVOT 文章标签： Oracle PIVOT
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xixi_666/article/details/103630395
版权
本文详细介绍了Oracle数据库中Pivot函数的使用方法，包括如何实现行转列操作，以及通过实例展示了Pivot函数在数据汇总和分析中的具体应用。同时，还提供了使用Max函数结合Decode或Case When进行行转列的替代方案。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 

--使用格式：

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.csdn.net/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（列名））

Oracle pivot函数动态行转列方法
m0_46292928的博客
06-09 3663
起因 工作中遇到个问题,需要支持动态行转列,方便报表展示。 起初这样写:???? select * from table_name1 a pivot (sum(nvl(citycode,0)) for co in (select distinct citycode from table_name1); ???????????????????????????????????????????????????????? ==>绝对报错..... 还有些文章用的只查询但是输出的是XML格式,本人愚钝
oracle pivot 使用遇到的问题（待分析查看报错原因）
m0_60311330的博客
10-23 1118
用oracle plsql编写存储过程的时候，遇到pivot使用的问题。 例子代码在这里：oracle pivot unpivot 行列互换 select * from test002; select * from test002 t pivot(max(t.col_int) for id_test in('apple', 'banana', 'pear', 'orange', 'peach')); -- 在plsql developer 中执行以下代码报错：ora-01747 select
Oracle行转列之pivot
03-15
oracle数据库最详细的行转列资料，是官方文档的详细介绍版，中文的
oracle pivot/unpivot 查询
Uncle.Cui的技术博客
06-03 1431
oracle pivot unpivot 使用pivot之后的写法更加优雅，如下： pivot 的语法如下： unpivot的用法： 语法示例： SQL查询示例：
oracle pivot 不能用子查询,Oracle pivot in子查询的疑问
weixin_31244759的博客
04-07 2103
按照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增强包-解决原生PIVOT在不使用XML时无法in子查询的问题
DarkAthena的博客
12-25 2569
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 78
子查询 不能一步求解的时候使用子查询 单行子查询 单行操作符 示例：查询工资比SCOTT高的员工信息 SELECT * FROM emp WHERE sal > ( SELECT sal FROM emp WHERE ename = 'SCOTT' ); 注意点 1、括号 2、合理的书写风格 3、可以在主查询的where select h...
oracle pivot unpivot 行列互换
m0_60311330的博客
10-04 521
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实现动态行转列思路
不屈不挠的小强
01-25 4052
起因：因工作业务需要，需要实现A表和B表数据联查。 前提条件： 1.A表一条业务数据是一行的。 2.B表业务数据是动态多行。 3.一条A表数据对多条B表的数据 典型的一对多关系，A表的主键存储在B表中，通过A表的主键能查询到所有条数关联的业务数据。 这样就需要实现写sql实现。 原由：一般的认知是pivot函数支持固定列的行转列。支持动态的行转列需要转变下思路。 实现如下： select * from A a inner join (
oracle行转列函数pivot
咖啡杯里的茶
04-16 3112
什么情形下需要使用行转列 对于同一类的事物下具有多个属性(属性是有限的)，比如说学生选课，每一名可以选择多个课程，而且课程的数量也是有限重复的(对于不通学生选同一门科就相当于是课程重复)。比如说下面这样的数据： 姓名 班级 性别 课程 分数 张三 一班 男 高等数学 96 张三 一班 男 复变函数 78 张三 一班 男 英语 78 李四 二班 男 英语 98 李四 ...
oracle非常量不能用于privot_Oracle pivot in子查询的疑问
weixin_39860201的博客
01-13 477
按照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 986
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 232
展开全部pivot这个是sqlserver的函数啊，oracle中没有的。用这个吧：selectdiv_code,sum(casewhenpayout_kind_name='物价生活性32313133353236313431303231363533e4b893e5b19e31333433663036补贴’thenmoneyelse0end)物价生活性补贴,sum(cas...
oralce函数 PIVOT (聚合函数（列）for in（）) 行转列
爱笑才不是傻帽的博客
02-27 4866
PIVOT 语法为 SELECT <非透视的列>, [第一个透视的列],...[最后一个透视的列] FROM (<生成数据的 SELECT 查询>) PIVOT (<聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列],... [最后一个透视的列]) ) AS <透视表的别名&gt...
for oracle中pivot_如何在Oracle中PIVOT结果？
weixin_30539317的博客
02-06 255
问题：您要在Oracle中PIVOT结果。解PIVOT子句是Oracle Database 11g的新增功能，它使您可以将行翻转为查询输出中的列，同时，还可以对数据运行聚合函数。PIVOT对于查看大量数据的总体趋势特别有用。我们将使用销售数据来演示用法。示例SELECT*FROMsales;输出结果12008121998-02-286375521898.881200...
Oracle列转行函数Listagg以及pivot查询示例
weixin_30952535的博客
07-14 386
简单的Oracle列转行函数Listagg示例： CREATE TABLE tbl_test (catalog VARCHAR(1),product VARCHAR(2),amount NUMBER); INSERT INTO tbl_test VALUES('A','A1',1); INSERT INTO tbl_test VALUES('A','A1',2); INSERT...
oracle --高级函数应用-pivot （列转行）
u010464763的专栏
06-21 7515
oracle 高级函数应用
oracle中in函数在子查询使用,oracle 中 in 的用法
weixin_39867296的博客
04-03 622
你好：SQL:"IN"FunctionTheINfunctionhelpsreducetheneedtousemultipleORconditions.译：IN函数有助于减少OR条件的复合使用。ThesyntaxfortheINfunctionis:译：IN函数的语法：SELECTcolumnsFROMtablesWHEREcolumn1in(val...
oracle PIVOT
最新发布
01-11
### Oracle 数据库中的 PIVOT 操作 #### 定义与功能 PIVOT 操作允许数据从行格式转换为列格式，这通常用于创建交叉表或汇总报告。此操作可以简化复杂的数据分析需求并提高查询效率[^2]。 #### 基本语法结构 PIVOT 的基本语法规则如下： ```sql SELECT ... FROM table_name PIVOT ( aggregate_function(column_to_aggregate) FOR column_to_pivot IN (value_list) ); ``` 其中 `aggregate_function` 是聚合函数如 SUM() 或 COUNT(), 而 `column_to_aggregate` 则是要被聚合计算的字段名称；`column_to_pivot` 表示要旋转成新列的那个原始列的名字；最后 `(value_list)` 中包含了希望作为输出的新列头的具体取值列表[^4]。 #### 实际案例展示 假设有一个销售记录表格 sales_data, 结构如下: | Year | Quarter | Sales | |------|---------|-------| | 2021 | Q1 | 150 | | 2021 | Q2 | 200 | | 2021 | Q3 | 250 | | 2021 | Q4 | 300 | 如果想要按年份显示每季度销售额，则可编写如下 SQL 查询来实现这一目标: ```sql SELECT * FROM sales_data PIVOT ( SUM(Sales) AS Total_Sales FOR Quarter IN ('Q1' AS "FirstQuarter", 'Q2' AS "SecondQuarter", 'Q3' AS "ThirdQuarter", 'Q4' AS "FourthQuarter") ); ``` 执行上述命令之后将会得到这样的结果集: | YEAR | FirstQuarter_Total_Sales | SecondQuarter_Total_Sales | ThirdQuarter_Total_Sales | FourthQuarter_Total_Sales | |------|--------------------------|----------------------------|---------------------------|-----------------------------| | 2021 | 150 | 200 | 250 | 300| 这里不仅实现了行列之间的变换还利用了别名机制使得返回的结果更加直观易读[^3].
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值