CODEWITHSUNDEEP
sqloracle-databasepivot
Ciccotelli412
Ciccotelli412

Reputation: 51

Pivot Data into New Column Names - Oracle SQL

working in Oracle SQL and I have the following data set:

enter image description here

My goal is to get it into a dataset that looks like this, I'm assuming I will have to use some form of a pivot function, but I'm not sure how I would go about creating new column names that don't already exist in the same step:

enter image description here

I am only providing one example of my total dataset, there will be plenty of INDIV_ID records with a varying number of possible purchase dates (i.e., not every INDIV_ID will have 10 purchase dates. Some could have more, some could have less)

Thank you in advance,
Nick

Upvotes: 0

Views: 222

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You can use Conditional Aggregation in such a way that to create a select statement string to pivot those columns while enumerating through use of ROW_NUMBER() function for each individual date in order to assign different integer value dynamically even if there exists equal date values within a stored function which returns a value in SYS_REFCURSOR type such as

CREATE OR REPLACE FUNCTION Fn_Pivot_Table 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 prch_dt END ) AS  "Date_'||rn||'"'  , ',')
                   WITHIN GROUP ( ORDER BY rn )
                 
    INTO v_cols
    FROM ( SELECT prch_dt,ROW_NUMBER() OVER (PARTITION BY indiv_id ORDER BY prch_dt) AS rn FROM t );
      
  v_sql :='SELECT indiv_id,'|| v_cols ||
          '  FROM ( SELECT t.*,ROW_NUMBER() OVER (PARTITION BY indiv_id ORDER BY prch_dt) AS rn FROM t )
            GROUP BY indiv_id';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

where t represents your table purchase date values of which needs to be pivoted.

Then call that function from the SQL Developer's console as

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

SQL> PRINT result;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270843

You can do this if you know the number of columns you want:

select indiv_id,
       max(case when seqnum = 1 then prch_dt end) as dt_1,
       max(case when seqnum = 2 then prch_dt end) as dt_2,
       . . .
from (select t.*,
             row_number() over (partition by indiv_id order by prch_dt) as seqnum
      from t
     ) t
group by indiv_id;

If you don't know the number of columns, then you need to use dynamic SQL -- which is quite cumbersome.

Alternatively, you could aggregate the values into a single delimited string:

select indiv_id,
       list_agg(prch_dt, ',') within group (order by prch_dt)
from (select t.*,
             row_number() over (partition by indiv_id order by prch_dt) as seqnum
      from t
     ) t
group by indiv_id;

Upvotes: 2

Related Questions