oracle 在SQL中将行拆分为列

pnwntuvh 于 2023-10-16 发布在 Oracle

关注(0)|答案(2)|浏览(113)

| E1| E2| E3|解释|量|
| --|--|--|--|--|
| 01 |||人员开支| 5000 |
| 01 | 1 ||干事| 5000 |
| 01 | 1 | 1 |基本薪金| 3000 |
| 01 | 1 | 2 |增加及补偿| 2000 |
| 02 |||货物和服务采购费用| 8000 |
| 02 | 1 ||服务采购| 8000 |
| 02 | 1 | 1 |文具采购| 6000 |
| 02 | 1 | 2 |办公用品采购| 2000 |
我想使用数据库中保存的表获得第二种表格式的输出，如下所示。
| A1| A2| A3|量|
| --|--|--|--|
| 人员开支||| 5000 |
| 人员开支|干事|| 5000 |
| 人员开支|干事|基本薪金| 3000 |
| 人员开支|干事|增加及补偿| 2000 |
| 货物和服务采购费用||||
| 货物和服务采购费用|服务采购|| 8000 |
| 货物和服务采购费用|服务采购|文具采购| 6000 |
| 货物和服务采购费用|服务采购|办公用品采购| 2000 |
为此，我用SQL编写了一个手动代码，如下所示。但我怎样才能让它更有效率呢？

SELECT 
CASE WHEN E1 = '01' PERSONNEL EXPENSES'
     WHEN E1 = '02' THEN 'GOODS AND SERVICES PURCHASE EXPENSES'
     ELSE NULL END AS A1,
     
CASE WHEN E1 = '01' AND E2 = '1' THEN 'OFFICERS'
     WHEN E1 = '02' AND E2 = '1' THEN 'SERVICE PROCUREMENT'
     ELSE NULL END AS A2,

AMOUNT 
FROM EXPENSES

如何从EXPENSES.EXPLANATION表中的列中取出数据，而不像下面的代码那样编写解释？

oracle

来源：https://stackoverflow.com/questions/77235751/split-a-row-into-columns-in-sql

2条答案

按热度按时间

8fq7wneg1#

你真的同时使用PostgreSQL和Oracle吗？如果是这样的话，我对前者无能为力，但是-对于甲骨文，这里有一个选择。不过，看起来不太好。
样本数据表明，这是关于某种层次结构：

SQL> select * From test;

E1 E2 E3 EXPLANATION                              AMOUNT
-- -- -- ------------------------------------ ----------
01       personnel expenses                         5000
01 1     officers                                   5000
01 1  1  basic salaries                             3000
01 1  2  increases and compensation                 2000
02       goods and services purchase expenses       8000
02 1     service procurement                        8000
02 1  1  stationary purchases                       6000
02 1  2  office supplies purchases                  2000

8 rows selected.

阅读代码中的注解;他们解释了每个CTE（和最终的SELECT）的作用：

SQL> with temp as
  2  -- create hierarchy (two columns, PARENT and CHILD)
  3  (          select 'X' e1      , null e2     , 'master row' explanation, 0 amount from dual
  4   union all select e1          , 'X' e2      , explanation             ,   amount from test where e2 is null
  5   union all select e1 ||'-'||e2, e1          , explanation             ,   amount from test where e2 is not null and e3 is null
  6   union all select e3          , e1 ||'-'||e2, explanation             ,   amount from test where e2 is not null and e3 is not null
  7  ),
  8  temp2 as
  9  -- SYS_CONNECT_BY_PATH creates a whole path of all explanations
 10  (select level lvl,
 11     sys_connect_by_path(explanation, '/') pt,
 12     amount
 13   from temp
 14   where level > 1
 15   start with e2 is null
 16   connect by nocycle prior e1 = e2
 17  )
 18  -- finally, extract separate result columns
 19  select
 20         substr(pt, instr(pt, '/', 1, 2) + 1,
 21                    case when lvl = 2 then length(pt) else instr(pt, '/', 1, 3) - instr(pt, '/', 1, 2) - 1 end
 22               ) a1,
 23         substr(pt, instr(pt, '/', 1, 3) + 1,
 24                    case when lvl = 3 then length(pt) else instr(pt, '/', 1, 4) - instr(pt, '/', 1, 3) - 1 end
 25               ) a2,
 26         substr(pt, instr(pt, '/', 1, 4) + 1,
 27                    case when lvl = 4 then length(pt) else instr(pt, '/', 1, 5) - instr(pt, '/', 1, 4) - 1 end
 28               ) a3,
 29         amount
 30  from temp2 t;

测试结果：

A1                                   A2                        A3                                       AMOUNT
------------------------------------ ------------------------- ------------------------------------ ----------
personnel expenses                                                                                        5000
personnel expenses                   officers                                                             5000
personnel expenses                   officers                  basic salaries                             3000
personnel expenses                   officers                  increases and compensation                 2000
goods and services purchase expenses                                                                      8000
goods and services purchase expenses service procurement                                                  8000
goods and services purchase expenses service procurement       stationary purchases                       6000
goods and services purchase expenses service procurement       office supplies purchases                  2000

8 rows selected.

SQL>

赞(0）回复(0）举报 2023-10-16

uajslkp62#

如果是Oracle，则可以使用如下的MODEL子句。它快速可靠。

WITH      --  S a m p l e    D a t a 
    tbl (E1, E2, E3, EXPLANATION,   AMOUNT) AS
        (   Select '01', null, null, 'PERSONNEL EXPENSES', 5000 From Dual Union All
            Select '01',    1, null, 'OFFICERS', 5000 From Dual Union All
            Select '01',    1,    1, 'BASIC SALARIES', 3000 From Dual Union All
            Select '01',    1,    2, 'INCREASES AND COMPENSATION', 2000 From Dual Union All
            Select '02', null, null, 'GOODS AND SERVICES PURCHASE EXPENSES', 8000 From Dual Union All
            Select '02',    1, null, 'SERVICE PROCUREMENT', 8000 From Dual Union All
            Select '02',    1,    1, 'STATIONERY PURCHASES', 6000 From Dual Union All
            Select '02',    1,    2, 'OFFICE SUPPLIES PURCHASES', 2000 From Dual 
        )
--  M a i n    S Q L :
Select      A1, A2, A3, AMOUNT
From          ( Select  E1, E2, E3, EXPLANATION, EXPLANATION "A1",  EXPLANATION "A2", EXPLANATION "A3", AMOUNT
                From tbl 
              )
    MODEL   Partition By (E1)
            Dimension By (E2, E3)
            Measures     (A1, A2, A3, AMOUNT) 
   RULES  ( A1[ANY, ANY] = A1[Null, Null],
            A2[ANY, ANY] = Case When CV(E2) Is Null Then Null 
                           Else A2[CV(), null] 
                           End,
            A3[ANY, ANY] = Case When CV(E3) Is Null Then Null
                           Else A3[CV(), CV()]
                           End
              )
Order By E1, E2 Nulls First, E3 Nulls First
/*
--  R e s u l t :
A1                                   A2                                   A3                                       AMOUNT
------------------------------------ ------------------------------------ ------------------------------------ ----------
PERSONNEL EXPENSES                                                                                                   5000
PERSONNEL EXPENSES                   OFFICERS                                                                        5000
PERSONNEL EXPENSES                   OFFICERS                             BASIC SALARIES                             3000
PERSONNEL EXPENSES                   OFFICERS                             INCREASES AND COMPENSATION                 2000
GOODS AND SERVICES PURCHASE EXPENSES                                                                                 8000
GOODS AND SERVICES PURCHASE EXPENSES SERVICE PROCUREMENT                                                             8000
GOODS AND SERVICES PURCHASE EXPENSES SERVICE PROCUREMENT                  STATIONERY PURCHASES                       6000
GOODS AND SERVICES PURCHASE EXPENSES SERVICE PROCUREMENT                  OFFICE SUPPLIES PURCHASES                  2000
*/

赞(0）回复(0）举报 2023-10-16

我来回答

oracle 在SQL中将行拆分为列

2条答案

相关问题

热门标签

最新问答