jjzjj

Oracle行转列（pivot）和Oracle列转行（unpivot）

记录学习日常 2024-04-27 原文

        行变列，列变行在生成报表的时候经常遇到，行变列叫做"Pivot”, 反之叫做"Unpivot”。 在Oracle11g之前，一般都是通过case来实现，但是Oracle11g及其以后直接支持PIVOT和UNPIVOT的操作。

pivot

        语法：

        

SELECT * FROM （元数据）
PIVOT 
(
 SUM(Score/*行转列后 列的值*/) FOR 
 coursename/*需要行转列的列*/ IN (转换后列的值)
)

for 可以看成循环，for前是行转列后显示的值(这里必须使用聚合函数)，for后是循环的字段(一般是有限的固定值，如字典等) in (里是对for后的循环字段处理，比如起别名，做运算等)

         之前写sql的处理方式，遇到放在一起显示成一行的数据时，都是使用left join...连接成一条记录显示，这样效率不仅低而且sql看起来还很乱，如下所示：想将数据表按照数据来源统计求和

SELECT
        to_char(to_date(t1.tjrq,'yyyymmdd'),'yyyy"年"mm"月"dd"日"') tjrq,
        nvl ( t1.ztrs, 0 ) yjjj,
        nvl ( t2.ztrs, 0 ) qyxf,
        nvl ( t3.ztrs, 0 ) dgmhs
from
    (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM                                 
    A GROUP BY zrq, sjly, tjrq )
    where tjrq BETWEEN '20220814' and '20220914' and sjly='页面' GROUP BY sjly,tjrq) t1 
left join
    (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A         
     GROUP BY zrq, sjly, tjrq )
     where tjrq BETWEEN '20220814' and '20220914' and sjly='接口' GROUP BY sjly,tjrq) t2 on         
     t1.tjrq=t2.tjrq 
left join
     (SELECT tjrq,sum( ztrs ) ztrs FROM ( SELECT zrq, sjly, tjrq, min( ztrs ) ztrs FROM A     
     GROUP BY zrq, sjly, tjrq )
     where tjrq BETWEEN '20220814' and '20220914' and sjly='导入' GROUP BY sjly,tjrq) t3 on         
     t2.tjrq=t3.tjrq
order by t1.tjrq desc

虽说结果也是正确的，但无疑不是最优方案

使用pivot改造

select 
    to_char(to_date(tjrq,'yyyymmdd'),'yyyy"年"mm"月"dd"日"') tjrq,
    nvl ( yjjj, 0 ) yjjj,
    nvl ( qyxf, 0 ) qyxf,
    nvl ( dgmhs, 0 ) dgmhs
from 
(
	select sjly,tjrq,sum(ztrs) ztrs from (SELECT zrq, sjly,tjrq, min( ztrs ) ztrs FROM         
    A GROUP BY zrq, sjly ,tjrq) group by sjly,tjrq
) 
pivot (max(ztrs) for sjly in ('页面' as yjjj,'接口' as qyxf,'导入' as dgmhs))
where tjrq BETWEEN '20220814' and '20220914'
order by tjrq desc

教程例子：贴图助理解

 unpivot

        列转行，就是将一行结果拆成多行显示

语法：

select 字段 from 数据集
unpivot（自定义列名/*列的值*/ for 自定义列名 in（列名））

如上图

可以使用union 或者union all 

 

Oracle转行tjrqztrsxffjava数据库

有关Oracle行转列（pivot）和Oracle列转行（unpivot）的更多相关文章

  1. 如何判断oracle是否启动及启动oracle数据库 - 2

    plsql连接Oracle超时，完犊子了肯定是服务器断电了。得马上检查Oracle服务器状态1、检查数据库是否启动su-oracle切换到Oracle用户，输入sqlplus/assysdba显示连接状态。如果末尾显示的状态是Connectedtoanidleinstance.证明未启动2、启动数据库startup启动数据库，末尾出现Databaseopened说明数据库启动成功3、查看数据库监听是否正常先quit;断开Oracle连接，使用lsnrctlstatus查看监听状态，如果出现TNS-开头的Nolistener、Connectionrefused等错误，说明监听未启动4、启动数据库

  2. ruby - 使用 Ruby 连接到 Oracle 数据库 - 2

    我无法连接到OracleDB，阅读了很多资料但对结果没有帮助。我有远程OracleDB，我正在使用DBVisualizer设置连接连接到它，如下所示:DBType:OracleDriver(jdbc):OraclethinDatabaseURL:jdbc:oracle:thin:@10.10.100.10:1521/VVV.LOCALDOMAINUserIdf:SomeUserPass:SomePass连接正常。我在Ruby中所做的是:require'oci8'require'dbi'...conn=OCI8.new('SomeUser','SomePass','//10.10.100

  3. sql - ruby on rails 为 oracle View /函数准备的语句 - 2

    我有以下代码执行oracleView，如下所示:defrun_queryconnection.exec_query("SELECT*FROMTABLE(FN_REQRESP(#{type_param},#{search_type_param},#{tid_param},#{last_param},#{key_param},#{tran_id_param},#{num_param},#{start_date_param},#{end_date_param}))")end上述查询的输出如下:SELECT*FROMTABLE(FN_REQRESP('ALL','ALL_TRAN','1000

  4. C# 连接Oracle数据库方法 - 2

    C#连接oracle数据库常用的三种方法有三种：  一、Oracle.ManagedDataAccess.dll：Oracle官方提供的oracle数据库访问组件，不存在其他依赖dll库，推荐该方法最为重要的是：它不需要安装oracle客户端，也不区分x86位和x64位，直接就能用，连上其他电脑或者服务端oracle数据库。  二、Oracle.DataAccess.Client.dll：也叫ODP.net，是Oracle提供的数据库访问dll库。好处是：不需要安装客户端也能访问服务器上的oracle，不需要配置tnsNames.Ora文件，但是区分x86位和x64，也比较麻烦。  三、Sys

  5. ruby-on-rails - 如何使用 Oracle 配置 Ruby on Rails？ - 2

    网络上有多个页面对此进行了讨论，但大多数都已过时或在某些方面不准确。独家新闻是什么？ 最佳答案 构建ruby​​、gem和rails根据http://rubyonrails.org/download:buildrubybuildgemusegemtoinstallrails获取OracleInstantclient下载自https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html您的架构需要这

  6. javascript - Node.js + Oracle 9i 数据库 - 2

    我是node.js的新手，我已经进行了研究但没有成功，所以这是我的问题:可以将Node.js与Oracle9i数据库一起使用吗？如果是这样，我在哪里可以找到相关资料？提前致谢! 最佳答案 上面提到的npmjs现在列出:https://www.npmjs.org/search?q=db-oracle->https://github.com/mariano/node-db-oraclehttps://www.npmjs.org/search?q=oracle->https://github.com/nearinfinity/node-o

  7. sql - Oracle 11g 中对 JSON 的支持 - 2

    这个问题在这里已经有了答案:workwithjsoninoracle(6个答案)关闭6个月前。Oracle11g是否支持JSON？我指的是在PL/SQL查询中操作JSON对象之类的东西。我知道Oralcle12c支持这些东西，但11g似乎不支持。我主要关心的是是否可以在PL/SQL代码中处理从RESTfullAPI调用(使用UTL_HTTP包)返回的JSON对象。这就是我调用RESTfullAPI的方式:req:=UTL_HTTP.BEGIN_REQUEST('https://xxxx/api/job/all','GET','HTTP/1.1');UTL_HTTP.set_header

  8. Oracle 中常用的字符串函数总结 - 2

    一、substr函数——字符串截取格式1：substr(string,start_pos,length)格式2：substr(string,start_pos)说明：从指定位置start_pos截取字符串string的length位，如果不指定length（格式2）则从指定位置start_pos截取字符串string。start_pos等于0或者1均从左边第一位开始截取。如果start_pos为负整数，则从右截取字符串。举例子：SELECTsubstr('abcdef',0,3),substr('abcdef',1,3),substr('abcdef',3),substr('abcdef',-

  9. javascript - JavaScript/jQuery 中的 Oracle NVL 函数等价物 - 2

    在JavaScript/jQuery中是否有等效的OracleNVL函数。我有兴趣查看其工作原理的示例。 最佳答案 在Javascript中，这实际上可以由||运算符处理，返回第一个“有效”值。vara=null;varb="validvalue";varc=a||b;//c=="validvalue"请记住"falsy"值不仅可以是null，还可以是空字符串''、数字0和bool值false。因此，您需要确保要么考虑那些与null具有相同含义的变量，要么您的变量不能采用这些值，因为在这些情况下，您还会选择第二个值:vara="";

  10. oracle - 使用 Oracle 使用 INSERT 查询返回值 - 2

    如何在EXE语句中传递绑定(bind)参数值？例如-actualvalue=append(actualvalue,1)actualvalue=append(actualvalue,2)actualvalue=append(actualvalue,3)query=“insertintotable(a,b,c)values(:a,:b,:c)returningprimarykey,secondarykeyinto:primarykey,:secondarykey”stmtIns,err:=dbConnImbl.Prep(query)iferr!=nil{fmt.Println("Secon

随机推荐