将行转换为列的Oracle SQL

Question

我找不到合适的答案，所以我把它写在这里。我有一张包含以下字段的表格。

ID           Amount    DocNum   DocStatus   DueDate
AA           2400      00005     1          10-Jun-2019
AA           1400      00006     4          21-Sep-2019
AA           9000      00028     1          22-Aug-2020 
AA           5000      00201     2          31-Aug-2020
AA           6400      00410     1          22-Jan-2021
AA           2000      00511     1          01-Mar-2021
BB           1500      01390     1          01-Jan-2021

我想显示状态为1的的前3个最新文档

ID Document1 Amount1 Document2 Amount2 Document3 Amount3 
AA 00511     2000    00410     6400    00028     9000 
BB 01390     1500    XX        XX      XX        XX

我以为我可以使用Pivot或Decode，但无法确定其他条件。感谢您的帮助。

Answer 1

您可以使用row_number()和条件聚合：

select id,
max(case when seqnum = 1 then docnum end) as docnum_1,
max(case when seqnum = 1 then amount end) as amount_1,
max(case when seqnum = 2 then docnum end) as docnum_2,
max(case when seqnum = 2 then amount end) as amount_2,
max(case when seqnum = 3 then docnum end) as docnum_3,
max(case when seqnum = 3 then amount end) as amount_3
from (select t.*,
row_number() over (partition by id order by due_date desc) as seqnum
from t
where status = 1
) t
group by id;

Answer 2

alter session set nls_date_format='dd-Mon-yyyy';
with
my_table (id, amount, docnum, docstatus, duedate) as (
select 'AA', 2400, '00005', 1, to_date('10-Jun-2019') from dual union all
select 'AA', 1400, '00006', 4, to_date('21-Sep-2019') from dual union all
select 'AA', 9000, '00028', 1, to_date('22-Aug-2020') from dual union all
select 'AA', 5000, '00201', 2, to_date('31-Aug-2020') from dual union all
select 'AA', 6400, '00410', 1, to_date('22-Jan-2021') from dual union all
select 'AA', 2000, '00511', 1, to_date('01-Mar-2021') from dual union all
select 'BB', 1500, '01390', 1, to_date('01-Jan-2021') from dual
)
select id, "1_DOC" as document1, "1_AMT" as amount1,
"2_DOC" as document2, "2_AMT" as amount2,
"3_DOC" as document3, "3_AMT" as amount3
from   (
select id, amount, docnum, 
row_number() over (partition by id 
order by duedate desc) as rn
from   my_table
where  docstatus = 1
)
pivot  (min(docnum) as doc, min(amount) as amt for rn in (1, 2, 3))
;

ID DOCUMENT1    AMOUNT1 DOCUMENT2    AMOUNT2 DOCUMENT3    AMOUNT3
-- --------- ---------- --------- ---------- --------- ----------
AA 00511           2000 00410           6400 00028           9000
BB 01390           1500     

您需要在子查询中完成所有准备工作：筛选docstatus = 1，按duedate降序创建RN排名，然后只选择数据透视所需的列。除了琐碎的数据透视(在完成子查询中的所有准备工作之后是琐碎的(之外，外部查询只需要在select子句中稍微注意一下，就可以正确地获取列名。

Answer 3

您可以动态生成所需的SQL SELECT语句，以便调整行以显示顶部2、3、4。。通过创建这样一个具有IN参数的函数来表示顶部2、3、4。。等等，并返回CCD_ 6类型的结果集作为

CREATE OR REPLACE FUNCTION Fn_Pivot_Doc_and_Amounts( numcol INT ) RETURN SYS_REFCURSOR IS
v_recordset SYS_REFCURSOR;
v_sql       VARCHAR2(32767);
v_cols      VARCHAR2(32767);
BEGIN
SELECT LISTAGG( ''||level||' AS "'||level||'"' , ',' )
WITHIN GROUP ( ORDER BY level )
INTO v_cols
FROM dual
CONNECT BY level <= numcol;
v_sql :='SELECT *
FROM(SELECT id,docnum,amount,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY duedate DESC) AS rn
FROM tab t
WHERE docstatus = 1)
PIVOT(
MAX(docnum) AS document,
MAX(amount) AS amount  FOR rn IN ( '|| v_cols ||'  )
)';
OPEN v_recordset FOR v_sql;
RETURN v_recordset;
END;

然后从SQL Developer的控制台调用

SQL> DECLARE
result SYS_REFCURSOR;
BEGIN
:result := Fn_Pivot_Doc_and_Amounts(2); -- 3,4,...
END;
/
SQL> PRINT result;