Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic PL/SQL which reads table and transposes rows to columns (oracle 11g R2, windows)
Dynamic PL/SQL which reads table and transposes rows to columns [message #630231] Tue, 23 December 2014 06:33 Go to next message
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 #630235 is a reply to message #630231] Tue, 23 December 2014 06:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum,

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


BTW, what exactly is "dynamic" about the code you posted?

[Updated on: Tue, 23 December 2014 09:07]

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 Go to previous messageGo to next message
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

Re: Dynamic PL/SQL which reads table and transposes rows to columns [message #630280 is a reply to message #630275] Wed, 24 December 2014 01:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Previous Topic: Re:- how to check column value in particular format
Next Topic: sql query for employees based on Org hierarchy
Goto Forum:
  


Current Time: Fri Mar 29 10:07:13 CDT 2024