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 |
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 #664411 is a reply to message #664410] |
Sun, 16 July 2017 08:24 |
|
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 #664414 is a reply to message #664370] |
Sun, 16 July 2017 21:13 |
|
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 |
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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:54:12 CDT 2024
|