CODEWITHSUNDEEP
sqloracle-databaseplsqlunpivot
Srinath P
Srinath P

Reputation: 1

oracle unpivot multiple columns into multiple columns

i have a table with 10 columns. i want to convert this to 2 columns using unpivot. below are my tables. i tried with clause(to get aggrgate columns from all_tab_columns), unpivot but giving error simple column error or in clause error.COuld someone suggest where i am getting error.

input table
KEY_COL COL_A COL_B COL_C..till COL_J
1        a,b,c,d,e,f,g,h,i,j
2        x,y,a,s,f,g,e,r,g,d

output table
KEY_COL COLM VALUE
1   COL_A    a
1   COL_B    b
.
.
.
1  COL_J     j
2  COL_A     x
2  COL_B     y
.
.
.
2  COL_J    d

Any idea how to do this?

Upvotes: 0

Views: 959

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

In order to get Dynamic Results for Unpivoting, then using such a PL/SQL Block

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  val  VARCHAR2(4000);
  tbl  VARCHAR2(40)  := 'TAB';
BEGIN
  DBMS_OUTPUT.PUT_LINE('KEY_COL    COLM      VALUE ');  
  DBMS_OUTPUT.PUT_LINE('-------    -----     ------'); 
  FOR c IN (  
              SELECT t.*, c.* 
                FROM user_tab_columns c
               CROSS JOIN tab t
               WHERE table_name = tbl AND column_id > 1
               ORDER BY t.key_col, c.COLUMN_ID  
           )
  LOOP 
      EXECUTE IMMEDIATE 'SELECT '||c.column_name||' FROM '||tbl||' WHERE key_col = :kc' 
                   INTO val 
                  USING c.key_col;  

      DBMS_OUTPUT.PUT_LINE(c.key_col||'          '||c.column_name||'     '||val);        
  END LOOP;
END;   
/

may help to extract each column values from dictionay view user_tab_columns provided that the table is within your schema. Otherwise that view should be replaced with all_tab_columns by specifying the owner.

The query of the cursor loops for getting all values of the sample table except the key column.

Demo

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You can use the UNPIVOT clause as follows:

SQL> --sample data
SQL> with your_Table(id,cola,colb)
  2  as (select 1,'a','b' from dual union all
  3  select 2,'c','d' from dual)
  4  -- your query starts from here
  5  select * from your_Table
  6  unpivot (dataa for col in (cola,colb)); -- you need to pass 10 columns here

        ID COL  D
---------- ---- -
         1 COLA a
         1 COLB b
         2 COLA c
         2 COLB d

SQL>

I have used just 2 columns, You need to extend it to use 10 columns

Or another approach, You can use UNION ALL 10 times, It will be lengthy approach though.

SQL> --sample data
SQL> with your_Table(id,cola,colb)
  2  as (select 1,'a','b' from dual union all
  3  select 2,'c','d' from dual)
  4  -- your query starts from here
  5  select ID, 'COLA' AS COL, COLA from your_Table
  6  UNION ALL
  7  select ID, 'COLB' AS COL, COLB from your_Table
  8  ORDER BY ID, COL;

        ID COL  C
---------- ---- -
         1 COLA a
         1 COLB b
         2 COLA c
         2 COLB d

SQL>

Upvotes: 1

Related Questions