CODEWITHSUNDEEP
sqloracle-databaseunpivot
amp609
amp609

Reputation: 61

How to unpivot a single row in Oracle 11?

I have a row of data and I want to turn this row into a column so I can use a cursor to run through the data one by one. I have tried to use 

SELECT * FROM TABLE(PIVOT(TEMPROW))

but I get

'PIVOT' Invalid Identifier error.

I have also tried that same syntax but with 

('select * from TEMPROW')

Everything I see using pivot is always using count or sum but I just want this one single row of all varchar2 to turn into a column.

My row would look something like this:

ABC | 123 | aaa | bbb | 111 | 222 |

And I need it to turn into this:

ABC

123

aaa

bbb

111

222

My code is similar to this:

BEGIN
    OPEN C_1 FOR SELECT * FROM TABLE(PIVOT( 'SELECT * FROM TEMPROW'));
        LOOP
            FETCH C_1 INTO TEMPDATA;
            EXIT WHEN C_2%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(1);
         END LOOP;
    CLOSE C_1;
END;

Upvotes: 0

Views: 1665

Answers (2)

Himanshu
Himanshu

Reputation: 3970

You have to unpivot to convert whole row into 1 single column

     select * from Table
     UNPIVOT
     (col for col in (
       'ABC' , '123' , 'aaa' ,' bbb' , '111' , '222' 
       ))

or use union but for that you need to add col names manually like

Select * from ( Select col1 from table 
 union
 select col2 from table union...
 Select coln from table)

sample output to show as below

enter image description here

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

One option for unpivoting would be numbering columns by decode() and cross join with the query containing the column numbers :

select decode(myId, 1, col1,
                    2, col2,
                    3, col3,
                    4, col4,
                    5, col5,
                    6, col6 ) as result_col
  from temprow
  cross join (select level AS myId FROM dual CONNECT BY level <= 6 );

or use a query with unpivot keyword by considering the common expression for the column ( namely col in this case ) must have same datatype as corresponding expression :

select result_col from
(
select col1, to_char(col2) as col2, col3, col4, 
       to_char(col5) as col5, to_char(col6) as col6  
  from temprow
 ) 
 unpivot (result_col for col in (col1,col2,col3,col4,col5,col6));

Demo

Upvotes: 0

Related Questions