oracle unpivot multiple columns into multiple columns

Question

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?

yes you already mentioned about ..._tab_columns view(s) that I lately realised. — Barbaros Özhan
– Barbaros Özhan, Commented Jul 8, 2020 at 8:40

Popeye · Accepted Answer · 2020-07-08 05:57:20Z

1

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>

edited Jul 8, 2020 at 5:57

answered Jul 8, 2020 at 5:41

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Srinath P Over a year ago

thank you tejash. i am making general solution. dynamic columns. i have to convert many tables. i am thinking for listagg function for_in clause. not hardcoding.

Popeye Over a year ago

So I have given answer for - i have a table with 10 columns. i want to convert this to 2 columns using unpivot.

Popeye Over a year ago

Oracle does not allow the dynamic column list in the IN clause. You should use the dynamic query then

Barbaros Özhan Over a year ago

Do you have a table with ten columns or one column containing values seperated by commas? If the first one is your choice as stated in the question, then listagg() function has no sense @SrinathP , and what Tejash told is exactly true for standard SQL case. Perhaps you can look up Dynamic SQL throuh dictionary views such as user_tab_columns.

Srinath P Over a year ago

@BarbarosÖzhan - i gave an example of one table with ten columns. i have more tables with different number of columns. so thinking for dynamic solution. yes i am using user_tab_columns. i already mentioned that question.

Barbaros Özhan · Accepted Answer · 2020-07-08 08:57:57Z

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

Collectives™ on Stack Overflow

oracle unpivot multiple columns into multiple columns

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related