行列互换-高级语法-oracle

我最近在做一个Java web项目，其中某个模块设计需要展示数据表格和图标，如图，

而数据库中数据存在2张表中（数据版本为oracle 11g）：

• 表1 存储的配置表 ，图示：

• 表2存储的为数据（数值），图示：

那么我们改如何把表中数据转换为上面我们我们想要展示的数据呢?

通过观察发现：

1. 目标数据3个单位列对应我们表2中的3个单位id

2. 目标数据的类别类对应表2中xm2列

3. 目标数值对应表2中的n1列

4. 目标数据最后一行数据为dm ‘0306’ ‘0307’ ‘0308’ 的3行之和

分析：

• 想要实现多行合并一行（对应列相加)，并没有太好的办法，但是我们经常做多列合并为一列的操作，对的这里我们先把列(xm2)行转列，把行合并转为列合并（相加），就实现了上面的4
• 此时查询结果和目标数据完全的行列倒置了，我们需要实现行列互换
• 首先列转行，既类别列转为行
• 接下来行转列，把单位行转为列
• 最后数据排序达到我们需要的数据

先介绍下Oracle相关的基础知识

1、行转列

pivot

语法：

select * from (select 字段 from 表 where 条件) unpivot (聚合函数(字段) for 字段 in (值1 别名,值2 别名 ))

已上面合并三行为例：

SELECT DWID, GJJS, JS, GJG, ZJG, NVL(CJG, 0) + NVL(WW, 0) + NVL(FW, 0) CJYX
  FROM (SELECT DWID, N1, XM2
           FROM RPT_LDYG_007
          WHERE DWID IN ('10010001', '30200000', '10200000')
                AND NY = '202104'
                AND XM = '03')

PIVOT(MAX(N1)
   FOR XM2 IN('0302' AS GJJS, '0303' AS JS, '0304' AS GJG, '0305' AS ZJG, '0306' AS CJG, '0307' AS WW, '0308' AS FW));

查询结果如图：

2、列转行

unpivot

语法：

select * from (select 字段 from 表 where 条件) unpivot (虚拟字段 for 字段 in (值1 别名,值2 别名 ))

离目标数据又仅了了一步，想要实现完全的行列倒置，我们先把类别列转为行：

SELECT *  FROM 
(SELECT dwid, gjjs, js, gjg, zjg, nvl(cjg, 0) + nvl(ww, 0) + nvl(fw, 0) cjyx
   FROM (SELECT dwid, n1, xm2
            FROM rpt_ldyg_007
           WHERE dwid IN ('10010001', '30200000', '10200000')
                 AND ny = '202104'
                 AND xm = '03')
         
 pivot(MAX(n1)
    FOR xm2 IN('0302' AS gjjs, '0303' AS js, '0304' AS gjg, '0305' AS zjg, '0306' AS cjg, '0307' AS ww, '0308' AS fw))
) 
unpivot(a FOR lb IN(gjjs, js, gjg, zjg, cjysq

查询结果如图：

3、行列互换

最后一步吧单位行转为列，这样得到我们想要的数据了

SELECT 'zy' LX, LB,
       DECODE(LB, 'GJJS', '高级技师', 'JS', '技师', 'GJG', '高级工', 'ZJG', '中级工', 'CJYX', '初级工及以下') MC, QYT, FGS, GLJ
  FROM (SELECT DWID, GJJS, JS, GJG, ZJG, NVL(CJG, 0) + NVL(WW, 0) + NVL(FW, 0) CJYX
           FROM (SELECT DWID, N1, XM2
                    FROM RPT_LDYG_007
                   WHERE DWID IN ('10010001', '30200000', '10200000')
                     AND NY = '202104'
                     AND XM = '03')
         
         PIVOT(MAX(N1)
            FOR XM2 IN('0302' AS GJJS,
                      '0303' AS JS,
                      '0304' AS GJG,
                      '0305' AS ZJG,
                      '0306' AS CJG,
                      '0307' AS WW,
                      '0308' AS FW))) UNPIVOT(A FOR LB IN(GJJS, JS, GJG, ZJG, CJYX))
PIVOT(MAX(A)
   FOR DWID IN('10010001' AS QYT, '30200000' AS FGS, '10200000' AS GLJ))

查询结果图示：

为什么加上LX这一列呢 ？因为有其他类型的数据需要一起查询为了区分加上的。

最后怎么保证顺序呢？ 我们想要的是 高级技师、 技师、高级工、中级工、初级工及以下这样的顺序

当然是通过order by 排序了，完整查询语句：

SELECT 'zy' LX, LB,
       DECODE(LB, 'GJJS', '高级技师', 'JS', '技师', 'GJG', '高级工', 'ZJG', '中级工', 'CJYX', '初级工及以下') MC, QYT, FGS, GLJ
  FROM (SELECT DWID, GJJS, JS, GJG, ZJG, NVL(CJG, 0) + NVL(WW, 0) + NVL(FW, 0) CJYX
           FROM (SELECT DWID, N1, XM2
                    FROM RPT_LDYG_007
                   WHERE DWID IN ('10010001', '30200000', '10200000')
                     AND NY = '202104'
                     AND XM = '03')
         
         PIVOT(MAX(N1)
            FOR XM2 IN('0302' AS GJJS,
                      '0303' AS JS,
                      '0304' AS GJG,
                      '0305' AS ZJG,
                      '0306' AS CJG,
                      '0307' AS WW,
                      '0308' AS FW))) UNPIVOT(A FOR LB IN(GJJS, JS, GJG, ZJG, CJYX))
PIVOT(MAX(A)
   FOR DWID IN('10010001' AS QYT, '30200000' AS FGS, '10200000' AS GLJ))
     order by DECODE(LB, 'GJJS', 1, 'JS',2, 'GJG', 3, 'ZJG', 4, 'CJYX', 5);

查询结果：