使用合计列进行动态行到列转换

Question

表名: sampledata

  id | State_Assign
-----------------
a10  | FL
a11  | AL
a11  | PH
a12  | MA
a12  | GL

我是甲骨文的新手，我已经尝试过了，但正在寻找动态的解决方案，状态分配的数量是动态的，它今天可以为a11 id设置2，但明天可以更改为3。

附件所需的put截图谢谢！

Answer 1

基本上，您可以使用这样一个包含条件聚合逻辑的静态解决方案

SELECT id,
       MAX(CASE WHEN rn = 1 THEN State_Assign END) AS sa1,
       MAX(CASE WHEN rn = 2 THEN State_Assign END) AS sa2,
       .......
       .......
       COUNT(*) AS total
  FROM (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY 0) AS rn 
          FROM sampledata s)
 GROUP BY id 
 ORDER BY id 

可以创建一个存储函数来生成select语句，该语句将根据表的当前值生成动态结果，例如

CREATE OR REPLACE FUNCTION Get_Pivoted_States RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX(CASE WHEN rn = '||rn||' THEN State_Assign END) AS sa'||rn ,',') 
          WITHIN GROUP (ORDER BY 0)        
    INTO v_cols
    FROM (SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS rn 
            FROM sampledata);

  v_sql :='SELECT id,
                  '|| v_cols ||',
                  COUNT(*) AS total
             FROM (SELECT s.*, ROW_NUMBER() OVER (PARTITION BY id ORDER BY 0) AS rn 
                     FROM sampledata s)
            GROUP BY id 
            ORDER BY id';

  OPEN v_recordset FOR v_sql;
  DBMS_OUTPUT.PUT_LINE(v_sql);
  RETURN v_recordset;
END;
/

可以从SQL开发人员控制台调用该函数，如下所示

SQL> DECLARE
    result SYS_REFCURSOR;
BEGIN
   :result := Get_Pivoted_States;
END;
/

SQL> PRINT result;