时间戳

首页 > 解决方案 > 如何将此混合行/列表转换为所需的输出。（Oracle SQL/发行版：Ora12c）

sql - 如何将此混合行/列表转换为所需的输出。（Oracle SQL/发行版：Ora12c）

问题描述

我有这个 Excel 输入文件被远程传递，并试图将它导入到这个最终结果中，但没有任何运气。任何帮助/指针将不胜感激。到目前为止，我已经尝试过 Oracle LEAD 函数来获取前 6 行的数据（如果在这种情况下这是正确的术语），其余的有问题。

所以我的传入数据采用这种格式（我在第 4 列的最后添加了行/行号，以使我的 LEAD 函数工作）。


+-----------+--------------+-------+------+
|   Col1    |     Col2     | Col3  | Col4 |
+-----------+--------------+-------+------+
| FYear     | 2019         |       | 1    |
| Office    | ABC          |       | 2    |
| Org       | xyz          |       | 3    |
| Acct      | 11122233     |       | 4    |
| SubAcct   | 555666       |       | 5    |
| Status    | C            |       | 6    |
| 1000      | blahblahblah | $1000 | 7    |
| 1001      | blahblahxxyy | $999  | 8    |
| 1029      | blahblahxxyy | $7676 | 9    |
| .....     | ..........   | ..... | ..   |
| FYear     | 2019         |       | 55   |
| Office    | EFG          |       | 56   |
| Org       | xyz          |       | 57   |
| Acct      | 11122233     |       | 58   |
| SubAcct   | 555888       |       | 59   |
| Status    | C            |       | 60   |
| 1000      | blahblahblah | $4440 | 61   |
| 1001      | blahblahxxyy | $3875 | 62   |
| 1029      | blahblahxxyy | $5029 | 63   |
| ......... | ......       | ....  | ...  |
+-----------+--------------+-------+------+

我试图让它成为这种输出格式。


+------+------+------+--------+--------+------+------+--------------+-------+
| Col1 | Col2 | Col3 |  Col4  |  Col5  | Col6 | Col7 |     Col8     | Col9  |
+------+------+------+--------+--------+------+------+--------------+-------+
| 2019 | ABC  | xyz  | 112233 | 555666 | C    | 1000 | blahblahblah | $1000 |
| 2019 | ABC  | xyz  | 112233 | 555666 | C    | 1001 | blahblahxxyy | $999  |
| 2019 | ABC  | xyz  | 112233 | 555666 | C    | 1029 | blahblahxxyy | $7676 |
| ...  | ...  | ...  | ...    | ...    | .    | ...  | ...          | ...   |
| 2019 | EFG  | xyz  | 112233 | 555888 | C    | 1000 | blahblahblah | $4440 |
| 2019 | EFG  | xyz  | 112233 | 555888 | C    | 1001 | blahblahxxyy | $3875 |
| 2019 | EFG  | xyz  | 112233 | 555888 | C    | 1029 | blahblahxxyy | $5029 |
| ...  | ...  | ...  | ...    | ...    | .    | ...  | ...          | ...   |
+------+------+------+--------+--------+------+------+--------------+-------+

从本质上讲，我需要将前 6 行（如果你愿意的话，标题行）变成水平/重复值，以用于在它们下面的行号。然后在 n 上重复逻辑，因为这些标题部分不断重复，行号表在它们下面。

任何指针/帮助表示赞赏！

Ps：-这是我迄今为止尝试过/想出的。

select (CASE WHEN Col1 = 'FYear' THEN Col2 END) New_Col1, 
LEAD((CASE WHEN Col1 = 'Office' THEN Col2 END)) OVER (ORDER BY Col4) New_Col2,
LEAD((CASE WHEN Col1 = 'Org' THEN Col2 END),2,0) OVER (ORDER BY Col4) New_Col3,
LEAD((CASE WHEN Col1 = 'Acct' THEN Col2 END),3,0) OVER (ORDER BY Col4) New_Col4,
LEAD((CASE WHEN Col1 = 'SubAcct' THEN Col2 END),4,0) OVER (ORDER BY Col4) New_Col5,
LEAD((CASE WHEN Col1 = 'Status' THEN Col2 END),5,0) OVER (ORDER BY Col4) New_Col6
from demo_table
where col4 <7;

标签： sqloraclepivot

解决方案

我赞同行没有在数据库中排序的评论。此外，您没有指定段是否可以超过 9 行。

我有一个适用于任意数量行 pr 段的解决方案，如果订单以某种方式保留，它需要每个段以 col1 中的“FYear”开头，并保留订单，但不关心如果标题行之后正好有三行。

设置：

create table testtbl
(
  col1 varchar2(100)
  ,col2 varchar2(100)
  ,col3 varchar2(100)
  ,col4 int
  )
insert into testtbl values ('FYear'  ,2019,'',1);
insert into testtbl values ('Office' ,'ABC','',2);
insert into testtbl values ('Org'    ,'xyz','',3);
insert into testtbl values ('Acct'   ,11122233,'',4);
insert into testtbl values ('SubAcct',555666,'',5);
insert into testtbl values ('Status' ,'C','',6);
insert into testtbl values (1000     ,'blahblahblah',1000,7);
insert into testtbl values (1001     ,'blahblahxxyy',999,8);
insert into testtbl values (1029     ,'blahblahxxyy',7676,9);
insert into testtbl values ('FYear'  ,2019,'',10);
insert into testtbl values ('Office' ,'EFG','',11);
insert into testtbl values ('Org'    ,'xyz','',12);
insert into testtbl values ('Acct'   ,11122233,'',13);
insert into testtbl values ('SubAcct',555888,'',14);
insert into testtbl values ('Status' ,'C','',15);
insert into testtbl values (1000     ,'blahblahblah',4440,16);
insert into testtbl values (1001     ,'blahblahxxyy',3875,17);
insert into testtbl values (1029     ,'blahblahxxyy',5029,18);

解决方案：

我首先创建一个跨越序列中所有行的 grp，然后在该组中找到标题值并将它们放在前 6 列中。然后我将原来的 3 列添加为 col7-9，最后过滤掉包含标题的行。

select 
col1,col2,col3,col4,col5,col6,col7,col8,col9
from
(
select 
   max(case when col1='FYear' then Col2 else '' end)    over (partition by grp) Col1
  ,max(case when col1='Office' then Col2 else '' end)   over (partition by grp) Col2
  ,max(case when col1='Org' then Col2 else '' end)      over (partition by grp) Col3
  ,max(case when col1='Acct' then Col2 else '' end)     over (partition by grp) Col4
  ,max(case when col1='SubAcct' then Col2 else '' end)  over (partition by grp) Col5
  ,max(case when col1='Status' then Col2 else '' end)   over (partition by grp) Col6  
  , col1  col7
  , col2  col8
  , col3  col9
from 
(
select
  col1,col2,col3,col4
  ,sum(case when col1='FYear' then 1 else 0 end ) over (order by col4) grp
from testtbl t 
) a
) b
where  col7 not in('FYear','Office','Org','Acct','SubAcct','Status' )

推荐阅读