Dynamic row to column conversion with total column

Question

Table Name : sampledata

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

I'm new to oracle, i have tried Pivot but looking for dynamic solution , number of state assign is dynamic it can 2 today for a11 id but can change to 3 tomorrow. attached required out put screenshot Thanks!

Is there a maximum number of states that will ever be assigned? (Other than 50...) Trying to understand if you need it to be truly dynamic, so you don't know how many columns will be in the result set until runtime; which often indicates this should be done in an application/reporting layer. — Alex Poole, May 04 '21 at 11:37
yeah! column contains other than state values also, but those are 2 or 3 values. — Koti Raavi, May 04 '21 at 11:47

score 0 · Answer 1 · answered May 04 '21 at 17:42

Basically you can use such a static solution containing conditional aggregation logic

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

A stored function might be created in order to generate select statement which will produce dynamical results depending on the current values of the table such as

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

The function might be invoked from the SQL Developer's console as

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

SQL> PRINT result;

Thank you so much! it looks great solution. one question is how to we call function in select statement. im using below code it doesn't giving result. thanks select Get_Pivoted_States() from dual; — Koti Raavi, May 05 '21 at 03:29
you're welcome @KotiRaavi . Yes, you'd get a cursor but not the records row by row while you're using the current select statement. Need to create a **type** in order call with the syntax `SELECT * FROM TABLE(...)` as in the answers of this [question](https://stackoverflow.com/questions/13690110/function-return-sys-refcursor-call-from-sql-with-specific-columns) , but need to describe each column individually in this case, and that will corrupt the current dynamic manner. — Barbaros Özhan, May 05 '21 at 06:14

Dynamic row to column conversion with total column

1 Answers1