Oracle SQL 将行转换为列

您可以使用 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;

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',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',to_date('22-Jan-2021') from dual union all
    select 'AA',2000,'00511',to_date('01-Mar-2021') from dual union all
    select 'BB',1500,'01390',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,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,3))
;


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

您需要在子查询中完成所有准备工作：过滤 docstatus = 1，按 RN 降序创建 duedate 排名，并仅选择数据透视所需的列。除了微不足道的数据透视（在您完成子查询中的所有准备工作之后微不足道）之外，外部查询只需要在 select 子句中稍加注意，即可获得正确的列名。

您可以动态生成所需的 SQL SELECT 语句，以便通过创建这样一个带有 IN 参数的函数来表示前 2,3,4 ..etc 来旋转行以显示是否前 2,4 ..etc并返回 public function getStatename($state_id) { $this->db->select('state_name'); $this->db->where('state_id',$state_id); $row = $this->db->get('states')->row_array(); if($row) return $row['state_name']; else { //should handle the error since data not found } } 类型的结果集为

SYS_REFCURSOR

然后从SQL Developer的控制台调用

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,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;