Home » SQL & PL/SQL » SQL & PL/SQL » Displaying a single row into column (Oracle 10g)
Displaying a single row into column [message #580632] Tue, 26 March 2013 05:36 Go to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

I just want to see single rows data as column.
select * from emp where rownum=1;

EMPNO	ENAME	JOB	MGR	HIREDATE	    SAL	  COMM	DEPTNO
7369	SMITH	CLERK	7902	17-DEC-80 00-00-00  800		 20

How can it show as
column_name column_value
EMPNO         7369
ENAME         SMITH
JOB           CLERK and so on...


Please help me.

Regards,
Nathan
Re: Displaying a single row into column [message #580639 is a reply to message #580632] Tue, 26 March 2013 06:48 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Please Try this ::

SELECT 'Empno' "Column Name" , TO_CHAR(empno) "Column Value" FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'Ename' "Column Name",ename FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'Job' "Column Name",job FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'Mgr' "Column Name", TO_CHAR(mgr) FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'HireDate' "Column Name" ,TO_CHAR(hiredate) FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'Sal ' "Column Name" ,TO_CHAR(sal) FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'Comm' "Column Name" , TO_CHAR(comm) FROM  emp  WHERE ROWNUM=1
UNION ALL
SELECT  'DeptNo' "Column Name" , TO_CHAR(deptno) FROM  emp  WHERE ROWNUM=1


Regards,
Paules
Re: Displaying a single row into column [message #580641 is a reply to message #580632] Tue, 26 March 2013 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join your table with a row generator which number of lines is the number of columns of your table then display each column with the column of the same number/name.

Regards
Michel

[Updated on: Tue, 26 March 2013 07:05]

Report message to a moderator

Re: Displaying a single row into column [message #580642 is a reply to message #580641] Tue, 26 March 2013 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See this.

Regards
Michel
Re: Displaying a single row into column [message #580643 is a reply to message #580641] Tue, 26 March 2013 07:09 Go to previous messageGo to next message
Littlefoot
Messages: 19650
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I *think* that you might be after Tom Kyte's PRINT_TABLE procedure (Google for more info; visit asktom; here's the code):
create or replace procedure print_table (p_query in varchar2)
   authid current_user
is
   l_thecursor     integer default dbms_sql.open_cursor;
   l_columnvalue   varchar2 (4000);
   l_status        integer;
   l_desctbl       dbms_sql.desc_tab;
   l_colcnt        number;
begin
   execute immediate 'alter session set 
        nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

   dbms_sql.parse (l_thecursor, p_query, dbms_sql.native);
   dbms_sql.describe_columns (l_thecursor, l_colcnt, l_desctbl);

   for i in 1 .. l_colcnt
   loop
      dbms_sql.define_column (l_thecursor,
                              i,
                              l_columnvalue,
                              4000);
   end loop;

   l_status := dbms_sql.execute (l_thecursor);

   while (dbms_sql.fetch_rows (l_thecursor) > 0)
   loop
      for i in 1 .. l_colcnt
      loop
         dbms_sql.column_value (l_thecursor, i, l_columnvalue);
         dbms_output.
          put_line (
            rpad (l_desctbl (i).col_name, 30) || ': ' || l_columnvalue);
      end loop;

      dbms_output.put_line ('-----------------');
   end loop;

   execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
exception
   when others
   then
      execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';

      raise;
end;
/


Example:
SQL> exec print_table('select * from emp where deptno = 10');
EMPNO                         : 7782
ENAME                         : CLARK
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 09-jun-1981 00:00:00
SAL                           : 3250
COMM                          :
DEPTNO                        : 10
-----------------
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
MGR                           :
HIREDATE                      : 17-nov-1981 00:00:00
SAL                           : 5800
COMM                          :
DEPTNO                        : 10
-----------------
EMPNO                         : 7934
ENAME                         : MILLER
JOB                           : CLERK
MGR                           : 7782
HIREDATE                      : 23-dec-1982 00:00:00
SAL                           : 2100
COMM                          :
DEPTNO                        : 10
-----------------

PL/SQL procedure successfully completed.

SQL>
Re: Displaying a single row into column [message #580644 is a reply to message #580643] Tue, 26 March 2013 07:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2036
Registered: January 2010
Senior Member
What's wrong with simple:

WITH t AS (
           SELECT * FROM emp WHERE rownum = 1
          )
SELECT  CASE column_value
          WHEN 1 THEN 'Empno'
          WHEN 2 THEN 'Ename'
          WHEN 3 THEN 'Job'
          WHEN 4 THEN 'Mgr'
          WHEN 5 THEN 'HireDate'
          WHEN 6 THEN 'Sal'
          WHEN 7 THEN 'Comm'
          ELSE 'DeptNo'
        END "Column Name",
        CASE column_value
          WHEN 1 THEN TO_CHAR(Empno)
          WHEN 2 THEN Ename
          WHEN 3 THEN Job
          WHEN 4 THEN TO_CHAR(Mgr)
          WHEN 5 THEN TO_CHAR(HireDate)
          WHEN 6 THEN TO_CHAR(Sal)
          WHEN 7 THEN TO_CHAR(Comm)
          ELSE TO_CHAR(DeptNo)
        END "Column Value"
  FROM  t,
        TABLE(sys.OdciNumberList(1,2,3,4,5,6,7,8))
  ORDER BY column_value
/

Column N Column Value
-------- --------------
Empno    7369
Ename    SMITH
Job      CLERK
Mgr      7902
HireDate 17-DEC-80
Sal      800
Comm
DeptNo   20

8 rows selected.

SQL> 


SY.
Re: Displaying a single row into column [message #580645 is a reply to message #580643] Tue, 26 March 2013 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or, if the input table is fixed:
SQL> with lines as (select level line from dual connect by level<=8),
  2       data as (select * from emp where rownum=1)
  3  select decode(line, 1,'EMPNO', 2,'ENAME', 3,'JOB', 4,'MGR', 5,'HIREDATE') col,
  4         decode(line, 1,to_char(EMPNO), 2,ENAME, 3,JOB, 4,MGR, 5,to_char(HIREDATE)) value
  5  from data, lines
  6  order by line
  7  /
COL      VALUE
-------- ----------------------------------------
EMPNO    7369
ENAME    SMITH
JOB      CLERK
MGR      7902
HIREDATE 17/12/1980 00:00:00

Regards
Michel
Re: Displaying a single row into column [message #580650 is a reply to message #580645] Tue, 26 March 2013 09:10 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

Thank you very much to all.I got a lot's of solutions and ideas from you guys.I hope that I won't loose my job as long as you greats are there. Cool

Regards,
Nathan
Re: Displaying a single row into column [message #580666 is a reply to message #580632] Tue, 26 March 2013 13:45 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Just for fun, another solution(!version dependent!):
select *
from 
   xmltable( '/ROWSET/ROW/*'
             passing xmltype(cursor(select * from hr.employees where rownum=1))
             columns
                tag varchar2(100) path 'name()'
               ,val varchar2(100) path '.'
      )
/

SQL> l
  1  select *
  2  from
  3     xmltable( '/ROWSET/ROW/*'
  4               passing xmltype(cursor(select * from hr.employees where rownum=1))
  5               columns
  6                  tag varchar2(100) path 'name()'
  7                 ,val varchar2(100) path '.'
  8*       )
SQL> /

TAG                         VAL
--------------------------- ------------------
EMPLOYEE_ID                 198
FIRST_NAME                  Donald
LAST_NAME                   OConnell
EMAIL                       DOCONNEL
PHONE_NUMBER                650.507.9833
HIRE_DATE                   21.06.07
JOB_ID                      SH_CLERK
SALARY                      2600
MANAGER_ID                  124
DEPARTMENT_ID               50


PS. before 11.2.0.3 can be troubles with xml rewriting. In this case you need to disable it:
alter session set events '19027 trace name context forever, level 0x1';

PPS. XML so buggy... don't use it in production
Re: Displaying a single row into column [message #580669 is a reply to message #580666] Tue, 26 March 2013 14:12 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

A little safer example with xml:
select *
from 
   xmltable( '/ROWSET/ROW/*'
             passing dbms_xmlgen.getxmltype('select * from hr.employees where rownum=1')
             columns
                tag varchar2(100) path 'name()'
               ,val varchar2(100) path '.'
      )
Re: Displaying a single row into column [message #581077 is a reply to message #580669] Mon, 01 April 2013 09:59 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

Hi All,

I have tried with paules,solomon,and Michels query.But as per the performance wise Michels code is fine.
I changed as per the code of Michel.
Now I got something new and tried to achieve the same by using Michel's code but I unable to do so.
If I am using union all then for each access of dual table costing 2 bytes.Can it is possible in any other way.
But no this way.
with temp as(
select 'data100' col1,'data200' col2,'data300' col3 from dual union all
select 'data101' col1,'data201' col2,'data301' col3 from dual union all
select 'data102' col1,'data202' col2,'data302' col3 from dual )
select * from temp;

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     3 |    81 |     6   (0)| 00:00:01 |
|   1 |  VIEW            |      |     3 |    81 |     6   (0)| 00:00:01 |
|   2 |   UNION-ALL      |      |       |       |            |          |
|   3 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |    FAST DUAL     |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------



Regards,
Nathan

[Updated on: Mon, 01 April 2013 10:05]

Report message to a moderator

Re: Displaying a single row into column [message #581079 is a reply to message #581077] Mon, 01 April 2013 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
each access of dual table costing 2 bytes.


2 BYTES? Where did you get this BYTES?
I think you should read the Database Performance Tuning Guide.

Regards
Michel
Re: Displaying a single row into column [message #581430 is a reply to message #581079] Sat, 06 April 2013 08:45 Go to previous messageGo to next message
sss111ind
Messages: 484
Registered: April 2012
Location: India
Senior Member

I beg your paradon that is not 2 bytes and thanks for the linke.But whether it is possible to code the above without using union all.

Regards,
Nathan
Re: Displaying a single row into column [message #581432 is a reply to message #581430] Sat, 06 April 2013 09:04 Go to previous message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are you trying to do?
What is the problem with union all?

Regards
Michel

[Updated on: Sat, 06 April 2013 09:05]

Report message to a moderator

Previous Topic: delete rows returned by complex join query
Next Topic: [ASK]Handle 0RA 23539
Goto Forum:
  


Current Time: Wed Oct 01 15:47:09 CDT 2014

Total time taken to generate the page: 0.10152 seconds