Dynamic PL/SQL which reads table and transposes rows to columns [message #630231] |
Tue, 23 December 2014 06:33 |
|
zulu69
Messages: 2 Registered: December 2014
|
Junior Member |
|
|
Hello Everyone,
AIM: The Target should be created with the columns transposed Dynamically with corresponding data
I have a table with columns like
product_num,
productstep_num,
routing_id,
item_num,
item_description,
item_planned_qty,
item_actual_qty,
operation,activity`
Product_num is the key column and for every Product_num we have Productstep_num's varying from 3 to 8 (may be 10 as well, which is not fixed)
(Note: productstep_num will have values like 10,20,30 .... 80 these productstep_num's will define the kind of process happening (like 10:raw materials, 20:grinding, 30: heating and so on)
So my Target should look like :
product_num,
step10_routing_id,
step10_item_num,
step10_item_description,
step10_item_planned_qty,
step10_item_actual_qty,
step10_operation,
step10_activity,
step20_item_num,
step20_item_description
and so on ......`
This is to do some Analytics R&D. Env : Oracle DB (11g R2)/Windows
PRODUCT_NUM STEP_NO ROUTING_ID MEANING ITEM DESC PLANNED_QTY ACTUAL_QTY OPERATION ACTIVITY
112 10 9 ing 904 Desiccant 8000 8000 1-pack RUN-TIME
112 10 9 ing 903 White Cotton 80 80 1-pack RUN-TIME
112 10 9 ing 901 Bottle 8000 8000 1-pack RUN-TIME
112 10 9 ing 201 Tablets 800000 800000 1-pack RUN-TIME
112 10 9 ing 8902 White Cap 8000 8000 1-pack RUN-TIME
112 10 9 ing 8905 Drug Label 8000 8000 1-pack RUN-TIME
112 10 9 ing 8906 Insert 8000 8000 1-pack RUN-TIME
112 20 9 Prod 8001 Tablets 8000 8000 1-QC QC
113 10 6 ing 9904 Drum 56.1 56.1 1-FILL RUN-TIME
113 20 6 ing 9801 Heavy Cream 95.37 95.37 BLEND RUN-TIME
113 20 6 ing 9802 Sugar 80.5 80.5 BLEND RUN-TIME
113 20 6 ing 9410 Blend 28 28 BLEND RUN-TIME
113 50 6 Prod 9310 Ice Cream 2805 2805 1-QC QC
I created this Procedure :
CREATE OR replace PROCEDURE Sample_gme_dynamic_test
AS
l_count NUMBER := 0;
l_col_name VARCHAR2(80) := NULL;
l_batch_no VARCHAR2(50) := NULL;
l_batchstep_no VARCHAR2(200) := NULL;
column_exists NUMBER := 0;
CURSOR c_batch_no IS
SELECT batch_no,
batchstep_no
FROM sample_gme_test;
CURSOR col_values IS
SELECT column_name
FROM all_tab_cols
WHERE table_name = 'SAMPLE_GME_TEST' --and nullable='N'
AND column_name NOT IN ( 'BATCH_NO', 'BATCHSTEP_NO' );
BEGIN
FOR j IN c_batch_no LOOP /*
BEGIN
select batch_no,batchstep_no into l_batch_no,l_batchstep_no -- gettn multiple batchstep_no's for Batch_no, for every batch_no : col_values loop must run
from SAMPLE_GME_TEST
where batch_no = j.batch_no;
dbms_output.put_line(l_batch_no||' '||l_batchstep_no);
END;*/
FOR i IN col_values LOOP
BEGIN
column_exists := 0;
dbms_output.Put_line(i.column_name);
l_col_name := i.column_name
||'_'
||j.batchstep_no;
SELECT Count(*)
INTO column_exists
FROM all_tab_cols
WHERE table_name = 'SAMPLE_GME_TRGT'
AND column_name = 'l_col_name';
IF( column_exists = 0 ) THEN
EXECUTE IMMEDIATE
'ALTER TABLE GME_TRGT ADD (l_col_name varchar2(100))';
--dbms_output.put_line('ALTER TABLE SAMPLE_GME_TRGT ADD (l_col_name varchar2(100)'); -- adds columns
END IF;
END;
END LOOP;
END LOOP;
END sample_gme_dynamic_test;
but its not working the way I expected, not getting desired result. I created Target table with only product_num column and inserted those two records.
Output: product_num l_col_name 112 113
*BlackSwan added {code} tags. Please do so yourself in the future.
[Updated on: Tue, 23 December 2014 06:47] by Moderator Report message to a moderator
|
|
|
|
Re: Dynamic PL/SQL which reads table and transposes rows to columns [message #630275 is a reply to message #630235] |
Wed, 24 December 2014 01:00 |
|
zulu69
Messages: 2 Registered: December 2014
|
Junior Member |
|
|
Hi,
Dynamic in the sense, the SQL Procedure/Package should create the Target table and transpose the respective rows to columns on the run and load the data accordingly.
We do not know how many productstep_num's might come for each product_num , so the distinct combination of product_num & productstep_num ... those many times the other columns should get created.
Eg :
Target structure should look like :
PRODUCT_NO PRODUCTSTEP_NO STEP10_ROUTING_ID STEP10_LINE_TYPE_MEANING STEP10_ITEM STEP10_DESCRIPTION STEP10_UOM STEP10_INTERMEDIATE_IN_QTY STEP10_INTERMEDIATE_OUT_QTY STEP10_YIELD_PERCENTAGE STEP10_CUMULATIVE_YIELD STEP10_PLAN_QTY STEP10_ACTUAL_QTY STEP10_MATERIAL_DETAIL_ID STEP10_OPERATION STEP10_ACTIVITY STEP20_ROUTING_ID STEP20_LINE_TYPE_MEANING STEP20_ITEM STEP20_DESCRIPTION STEP20_UOM STEP20_INTERMEDIATE_IN_QTY STEP20_INTERMEDIATE_OUT_QTY STEP20_YIELD_PERCENTAGE STEP20_CUMULATIVE_YIELD STEP20_PLAN_QTY STEP20_ACTUAL_QTY STEP20_MATERIAL_DETAIL_ID STEP20_OPERATION STEP20_ACTIVITY STEP30_ROUTING_ID STEP30_LINE_TYPE_MEANING STEP30_ITEM STEP30_DESCRIPTION STEP30_UOM STEP30_INTERMEDIATE_IN_QTY STEP30_INTERMEDIATE_OUT_QTY STEP30_YIELD_PERCENTAGE STEP30_CUMULATIVE_YIELD STEP30_PLAN_QTY STEP30_ACTUAL_QTY STEP30_MATERIAL_DETAIL_ID STEP30_OPERATION STEP30_ACTIVITY .....
[Updated on: Wed, 24 December 2014 01:08] Report message to a moderator
|
|
|
|