首页 > 数据库 >oracle中的行转列,列转行

oracle中的行转列,列转行

时间:2022-10-04 11:14:31浏览次数:57  
标签:case q1 转行 when 转列 sum oracle amt select

行转列:
源表:

 

 case when:

select y,
sum(case when q=1 then amt end) q1,
sum(case when q=2 then amt end) q2,
sum(case when q=3 then amt end) q3,
sum(case when q=4 then amt end) q4
from test04 group by y;

效果:

 

 pivot:

select * from test04
pivot(
sum(amt) for q in(1 as q1,2 as q2,3 as q3,4 as q4))

效果:

 

 

列转行:

unpivot:unpivot(新列名 for 聚合列名 in (对应的列名1…列名n ))

select * from (
select * from test04
pivot(
sum(amt) for q in(1 as q1,2 as q2,3 as q3,4 as q4)))           --沿用上面行转列的基础上,进行列转行回来
unpivot
(amt for q in(q1,q2,q3,q4))

效果:

 

 

标签:case,q1,转行,when,转列,sum,oracle,amt,select
From: https://www.cnblogs.com/stanzhou47/p/16753427.html

相关文章

  • [Oracle] LeetCode 48 Rotate Image 思维
    Youaregivenannxn2Dmatrixrepresentinganimage,rotatetheimageby90degrees(clockwise).Youhavetorotatetheimagein-place,whichmeansyouhave......
  • [Oracle] LeetCode 141 Linked List Cycle 判环
    Givenhead,theheadofalinkedlist,determineifthelinkedlisthasacycleinit.Thereisacycleinalinkedlistifthereissomenodeinthelistthat......
  • [Oracle] LeetCode 2 Add Two Numbers
    Youaregiventwonon-emptylinkedlistsrepresentingtwonon-negativeintegers.Thedigitsarestoredinreverseorder,andeachoftheirnodescontainsasin......
  • [Oracle] LeetCode 54 Spiral Matrix 模拟
    Givenanmxnmatrix,returnallelementsofthematrixinspiralorder.Solution点击查看代码classSolution{public:vector<int>spiralOrder(vector<ve......
  • oracle数据库运行内存PGA+SGA分配
    调整内存大小用dba身份进入oracle,(sqlplussys/密码assysdba):--显示内存分配情况showparametersga;--修改占用内存的大小altersystemsetsga_max_size=200mscop......
  • [Oracle] LeetCode 53 Maximum Subarray 贪心
    Givenanintegerarraynums,findthecontiguoussubarray(containingatleastonenumber)whichhasthelargestsumandreturnitssum.Asubarrayisacontig......
  • #yyds干货盘点#oracle常见面试题
    数据库对象:表(table)视图(view)序列(sequence)索引(index)同义词(synonym)1.视图:存储起来的select语句createviewemp_vwasselectemployee_id,last_name,salary......
  • Oracle 托管文件Oracle Managed Files
    什么是Oracle托管文件?使用Oracle托管文件可以简化Oracle数据库的管理。OracleManagedFiles使您(DBA)无需直接管理构成Oracle数据库的操作系统文件。使用Oracle......
  • Oracle 19C 安装及基本操作
    Oracle19c安装包地址:https://pan.baidu.com/s/1CnbkJMLOvPN7rZNUEtttdg 提取码:p00sRedHat7.4安装包地址:https://pan.baidu.com/s/1gNg4B4L2bplkQj46ksyH7A 提取码......
  • [Oracle] LeetCode 37 Sudoku Solver
    WriteaprogramtosolveaSudokupuzzlebyfillingtheemptycells.Asudokusolutionmustsatisfyallofthefollowingrules:Eachofthedigits1-9mustoc......