CODEWITHSUNDEEP
oracle-databaseplsql
Hunter Nelson
Hunter Nelson

Reputation: 1995

Convert single row to single column in oracle

Is it possible to have this statement return as a column instead of a row? Possibly using a pivot query?

select * from MYTABLE Where SOMECOLUMN = RESULT THAT RETURNS ONE RECORD

Upvotes: 0

Views: 1938

Answers (2)

S.Bozzoni
S.Bozzoni

Reputation: 1002

I made a simple function that makes what you want, it returns a table so you can invoke it as : Select * from TABLE(mypkg.RowToCol) , as example it will return a row of the EMP table wich EName is "KING".

create or replace PACKAGE mypkg AS
  TYPE ColTypRec IS RECORD (field_value varchar2(100),field_name Varchar2(100));
  TYPE ColTyp IS TABLE of ColTypRec;
end;

FUNCTION RowToCol RETURN ColTyp PIPELINED AS
c sys_refcursor;
col_tochar_names varchar(500);
col_names        varchar(500);
data             ColTypRec;
begin
--usage select * from TABLE(mypkg.RowToCol)
SELECT RTRIM (XMLAGG (XMLELEMENT (e, 'TO_CHAR('||column_name||') as '||column_name || ',')).EXTRACT ('//text()'),   ',') , 
       RTRIM (XMLAGG (XMLELEMENT (e, column_name || ',')).EXTRACT ('//text()'),   ',') 
       INTO  col_tochar_names, col_names
FROM USER_TAB_COLS WHERE table_name='EMP';
--dbms_output.put_line(col_tochar_names);
--dbms_output.put_line(col_names);
Open c for  'SELECT fieldvalue,fieldname from ( ' ||
            ' SELECT '||col_tochar_names ||
            ' FROM emp where emp.ename=''KING'' '||
            ' ) '||
            ' UNPIVOT INCLUDE NULLS (fieldvalue FOR fieldname IN ('||col_names||')) '||
            'order by fieldname';
LOOP
  FETCH c INTO data;
  EXIT  WHEN c%NOTFOUND;
  PIPE  ROW(data);  
END LOOP;
CLOSE c;
end;
end; --Body Package

Upvotes: 1

S.Bozzoni
S.Bozzoni

Reputation: 1002

For your purpose you can create a dynamic SQl variabile string and execute it inside a stored procedure, that variabile should concatenate all the fields names you need in a pivot query, like the one i show you below this text.

As example we can have the emp table , as you can see we extract only one row from the table where emp.name='KING', then we transpose it using the UNPIVOT statement. The name of the columns we transpose are ENAME, JOBand EMPNO but we could transpose other columns. You have to convert the columns to the same data type, since they must fit a single column, i used TO_CHAR(empno) in the example. I returned the field name too in the main select but you can omit it. Hope it helps.

select  fieldvalue,fieldname from (
    SELECT ename,job,to_char(empno) as empno
    FROM emp where emp.ename='KING'
    )
    UNPIVOT (fieldvalue FOR fieldname IN (ename as 'ENAME', job as 'JOB', empno as 'EMPNO'))
    order by fieldname ;

Upvotes: 2

Related Questions