Home » SQL & PL/SQL » SQL & PL/SQL » Pivot columns and dynamically assign values based on column name (Oracle 12.1.0.2, Linux)
Pivot columns and dynamically assign values based on column name [message #664370] Thu, 13 July 2017 21:29 Go to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Hi All,

Hope everyone is doing great.

I've a below table.


create table tst_cust_rsp
as
with data_tbl as
(select '10' as cust_id, date '2015-03-04' as dt, '1' as col1, '0' as col2, '1' as col3_1, '0' as col3_2, '1' as col3_3, '23' as col4 from dual
 union all
 select '20' as cust_id, date '2015-04-13' as dt, '0' as col1, '1' as col2, '1' as col3_1, '0' as col3_2, '0' as col3_3, '43' as col4 from dual
 union all
 select '30' as cust_id, date '2016-07-12' as dt, '1' as col1, '0' as col2, '0' as col3_1, '1' as col3_2, '0' as col3_3, '' as col4 from dual
 )
 select * from data_tbl;

 select * from tst_cust_rsp;

CUST_ID	DT	        COL1	COL2	COL3_1	COL3_2	COL3_3	COL4
10	3/4/2015	1	0	1	0	1	23
20	4/13/2015	0	1	1	0	0	43
30	7/12/2016	1	0	0	1	0	



Now i need to pivot COL* fields to rows and dynamically assign the values based on the column name. Let us say the for the filed COL1 i need to use the value 'Q1', 'Q2' for COL2, 'Q3' for the columns COL3_1, COL3_2, COL3_3 [all these belong to same category] and 'Q4' for column COL4. I've written below PL/SQL block to assign these values by hardcoding the values based on the column name. Is there a way to achieve the result by keeping the column name and the values that need to be assigned in a table and read from that table instead of hard coding the values in the pl/sql block. Can someone please help me out on this?


 create table tst_transponse_data
  (cust_id number(10),
   dt date,
   assign_col_id varchar2(30), 
   col_val varchar2(30), 
   assign_col_name varchar2(30)
   );


 declare
    cursor cur is select * from tst_cust_rsp;
    type cur_rec_type is table of cur%rowtype;
    cur_rec cur_rec_type;
    type tmp_trans_type is table of tst_transponse_data%rowtype ;

    tmp_trans_rec_tab_holder        tmp_trans_type := tmp_trans_type();
    tmp_trans_rec_tab        tmp_trans_type := tmp_trans_type();
    
    
  v_c_last number default 0;
  v_s_last number default 0;
  v_total  number default 0;  
    
begin
     execute immediate 'truncate table tst_transponse_data';
  
  open cur;
  
  LOOP
        FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
        
        for i in 1 .. cur_rec.count loop
            tmp_trans_rec_tab_holder.delete;
          tmp_trans_rec_tab_holder.extend;
          v_s_last :=tmp_trans_rec_tab_holder.last;
                            
                   tmp_trans_rec_tab_holder(v_s_last).cust_id    := cur_rec(i).cust_id;
                   tmp_trans_rec_tab_holder(v_s_last).dt    := cur_rec(i).dt;
                   
                   IF  cur_rec(i).col1 is not null and cur_rec(i).col1 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q1';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := 'A1';
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL1';                  
                 END IF;
                 
                 IF  cur_rec(i).col2 is not null and cur_rec(i).col2 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q2';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := 'A2';
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL2';                  
                 END IF;
                 
                 IF  cur_rec(i).col3_1 is not null and cur_rec(i).col3_1 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q3';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := 'A3_1';
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL3_1';                  
                 END IF;
                 
                 IF  cur_rec(i).col3_2 is not null and cur_rec(i).col3_2 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q3';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := 'A3_2';
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL3_2';                  
                 END IF;
                 
                 IF  cur_rec(i).col3_3 is not null and cur_rec(i).col3_3 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q3';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := 'A3_3';
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL3_3';                  
                 END IF;
                 
                 IF  cur_rec(i).col4 is not null and cur_rec(i).col4 != '0' then
                       tmp_trans_rec_tab.extend;
                       v_c_last := tmp_trans_rec_tab.last;
                       tmp_trans_rec_tab(v_c_last):=tmp_trans_rec_tab_holder(v_s_last);
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_ID :='Q4';
                       tmp_trans_rec_tab(v_c_last).COl_VAL   := cur_rec(i).col4;  -- This one just need to move whatever the value on COL4 field
                       tmp_trans_rec_tab(v_c_last).ASSIGN_COL_NAME   := 'COL4';                  
                 END IF;           
                 
               end loop;
               
              IF tmp_trans_rec_tab.count > 0 then

                   FORALL i in INDICES OF tmp_trans_rec_tab
                     insert into tst_transponse_data
                       (cust_id, dt, assign_col_id, col_val, assign_col_name)
                     values (tmp_trans_rec_tab(i).cust_id, tmp_trans_rec_tab(i).dt, tmp_trans_rec_tab(i).assign_col_id, tmp_trans_rec_tab(i).col_val, tmp_trans_rec_tab(i).assign_col_name);
                     
                   v_total := v_total + SQL%ROWCOUNT;
                   
                   tmp_trans_rec_tab_holder.delete;
                   tmp_trans_rec_tab.delete;
                 END IF;
                 exit when cur%notfound;
               end loop;
               dbms_output.put_line('Total records inserted : '||v_total);
               commit;
end;
/

select * from tst_transponse_data;

CUST_ID	DT	  ASSIGN_COL_ID	COL_VAL	ASSIGN_COL_NAME
10	3/4/2015	Q1	A1	COL1
10	3/4/2015	Q3	A3_1	COL3_1
10	3/4/2015	Q3	A3_3	COL3_3
10	3/4/2015	Q4	23	COL4
20	4/13/2015	Q2	A2	COL2
20	4/13/2015	Q3	A3_1	COL3_1
20	4/13/2015	Q4	43	COL4
30	7/12/2016	Q1	A1	COL1
30	7/12/2016	Q3	A3_2	COL3_2




This the table that i'm talking about above to store the column names and the values that need to be assigned based on the column name.


 create table tst_ref_data
  (col_name varchar2(30),
   assign_col_id varchar2(30),
   col_val varchar2(30), 
   col_type varchar2(1)
   );
   
insert into tst_ref_data values ('COL1',   'Q1', 'A1', '');
insert into tst_ref_data values ('COL2',   'Q2', 'A2', '');
insert into tst_ref_data values ('COL3_1',   'Q3', 'A3_1', '');
insert into tst_ref_data values ('COL3_2',   'Q3', 'A3_2', '');
insert into tst_ref_data values ('COL3_3',   'Q3', 'A3_3', '');
insert into tst_ref_data values ('COL4',   'Q4', '', 'O');  -- If the col_type is O then assign the value that is received in the COL4 field
commit;

select * from tst_ref_data;

COL_NAME	ASSIGN_COL_ID	COL_VAL	COL_TYPE
COL1	        Q1	        A1	
COL2	        Q2	        A2	
COL3_1	        Q3	        A3_1	
COL3_2	        Q3	        A3_2	
COL3_3	        Q3	        A3_3	
COL4	        Q4		O



Appreciate your help!

Thanks
SS
Re: Pivot columns and dynamically assign values based on column name [message #664410 is a reply to message #664370] Sat, 15 July 2017 17:21 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Can someone please help me out on the above?

Appreciate your help!

Thanks
SS
Re: Pivot columns and dynamically assign values based on column name [message #664411 is a reply to message #664410] Sun, 16 July 2017 08:24 Go to previous messageGo to next message
live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India
Member
with t(a,b,c,d) as ( 
select * from (  
select * from   tst_cust_rsp
unpivot  (x for y in (col1,col2,col3_1,col3_2,col3_3,col4))
) where x <>0 )
select a,b,c
,decode(substr(UPPER(C),1,4),'COL1','Q1','COL2','Q2','COL3','Q3','COL4','Q4') AS ASSIGN_COL_ID,
REPLACE(C,'COL','A') AS COL_VAL	
FROM T;

10	04-MAR-15	COL1	Q1	A1
10	04-MAR-15	COL3_1	Q3	A3_1
10	04-MAR-15	COL3_3	Q3	A3_3
10	04-MAR-15	COL4	Q4	A4
20	13-APR-15	COL2	Q2	A2
20	13-APR-15	COL3_1	Q3	A3_1
20	13-APR-15	COL4	Q4	A4
30	12-JUL-16	COL1	Q1	A1
30	12-JUL-16	COL3_2	Q3	A3_2
Re: Pivot columns and dynamically assign values based on column name [message #664413 is a reply to message #664411] Sun, 16 July 2017 21:04 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Thank you live4learn for your help. My table has 100+ fields with few millions of rows. Will i run into any performance issues with unpivot?

Thanks
SS
Re: Pivot columns and dynamically assign values based on column name [message #664414 is a reply to message #664370] Sun, 16 July 2017 21:13 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
The following combines several methods that I have found from other Web Sites to produce the requested output.

The UNPIVOT command in list columns can be assigned to a variable so in this way the column list can be somewhat
dynamic so that this solution should work with your actual tables. The second list is help select the columns
to be included in report based on their value. This was added to accommodate the possibility that the "COL4"
in your example could be a 0 (zero). There is also a column "SRT" which has the value of "ROWNUM" for each of the
the rows produced from the unpivot of the "tst_cust_rsp" query. This is used for sorting and is not printed in the output.

Sample row to demonstrate where "COL4" has a value of 0.

INSERT INTO tst_cust_rsp VALUES (40,TO_DATE('7/16/2017','MM-DD-YYYY'), 0, 0, 0, 0, 0, 0);
COMMIT;

The following is intended and formatted to be saved and run as a single script, but it can be run as
individual lines. The output from the script will be much cleaner.

SET FEEDBACK  off;
SET LINESIZE  200;
SET TRIMSPOOL on;
SET VERIFY    off;
SET TERMOUT   off;

CLEAR COLUMNS;

-- Setup and assignment script variables.
COLUMN lv_up_in_list new_value lv_up_in_list;
COLUMN lv_cl_in_list new_value lv_cl_in_list;

SELECT 
    LISTAGG(col_name, ',')
        WITHIN GROUP (ORDER BY col_name) AS lv_up_in_list 
    FROM (SELECT DISTINCT col_name FROM tst_ref_data ORDER BY col_name);

SELECT 
    LISTAGG(CHR(39) || col_name || CHR(39), ',')
        WITHIN GROUP (ORDER BY col_name) AS lv_cl_in_list 
    FROM (SELECT DISTINCT col_name FROM tst_ref_data WHERE col_val IS NOT NULL ORDER BY col_name); 

-- Report output formatting and setup.
COLUMN assign_col_name FORMAT A15;
COLUMN assign_col_id   FORMAT A13;
COLUMN col_val         FORMAT A7;
COLUMN srt             NOPRINT;
SET FEEDBACK  on;
SET TERMOUT   on;

SELECT   srt
        ,rsp.cust_id                             AS cust_id
        ,TO_CHAR(rsp.dt, 'fmmm/dd/yyyy')         AS dt
        ,trd.assign_col_id                       AS assign_col_id
        ,NVL(trd.col_val, rsp.val)               AS col_val
        ,trd.col_name                            AS assign_col_name
FROM
       (SELECT   cust_id
                ,dt
                ,col
                ,val
                ,rownum                          AS srt
        FROM     tst_cust_rsp
        UNPIVOT (val FOR col IN (&lv_up_in_list))
        WHERE   (    col IN  (&lv_cl_in_list)
                 AND val != 0
                )
        OR      col NOT IN  (&lv_cl_in_list)
       )                                            rsp
      ,tst_ref_data                                 trd
WHERE  rsp.col = trd.col_name
ORDER BY srt;

Output from the script:

CUST_ID DT         ASSIGN_COL_ID COL_VAL ASSIGN_COL_NAME
------- ---------- ------------- ------- ---------------
10      3/4/2015   Q1            A1      COL1
10      3/4/2015   Q3            A3_1    COL3_1
10      3/4/2015   Q3            A3_3    COL3_3
10      3/4/2015   Q4            23      COL4
20      4/13/2015  Q2            A2      COL2
20      4/13/2015  Q3            A3_1    COL3_1
20      4/13/2015  Q4            43      COL4
30      7/12/2016  Q1            A1      COL1
30      7/12/2016  Q3            A3_2    COL3_2

9 rows selected.

Re: Pivot columns and dynamically assign values based on column name [message #664415 is a reply to message #664413] Sun, 16 July 2017 21:16 Go to previous messageGo to next message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
I modified the query to join to tst_ref_data to get the assign* fields from this table.


with t as ( 
select * from (  
select * from   tst_cust_rsp
unpivot  (col_value for col_name in (col1,col2,col3_1,col3_2,col3_3,col4))
) where col_value <>0 )
select a.cust_id, a.dt, a.col_name, a.col_value, b.assign_col_id, decode(b.col_type,'O',a.col_value,b.col_val) as col_val from t a inner join tst_ref_data b on (a.col_name = b.col_name);

CUST_ID	DT	COL_NAME	COL_VALUE	ASSIGN_COL_ID	COL_VAL
10	3/4/2015	COL1	1	Q1	A1
10	3/4/2015	COL3_1	1	Q3	A3_1
10	3/4/2015	COL3_3	1	Q3	A3_3
10	3/4/2015	COL4	23	Q4	23
20	4/13/2015	COL2	1	Q2	A2
20	4/13/2015	COL3_1	1	Q3	A3_1
20	4/13/2015	COL4	43	Q4	43
30	7/12/2016	COL1	1	Q1	A1
30	7/12/2016	COL3_2	1	Q3	A3_2


Re: Pivot columns and dynamically assign values based on column name [message #664416 is a reply to message #664415] Sun, 16 July 2017 21:18 Go to previous message
sspn2010
Messages: 167
Registered: October 2008
Senior Member
Sorry mike, i just saw your reply. Thank you for your help and the query.
Previous Topic: Case statement in From Clause
Next Topic: Solution for ORA-06550: line 6, column 31: PLS-00103: Encountered the symbol ")" when expecting one
Goto Forum:
  


Current Time: Fri Dec 15 04:11:21 CST 2017

Total time taken to generate the page: 0.01591 seconds