format sqlplus output

Hi there,

how can I achive that sqlplus does not output in columns

Is there simple way to do ?

select A,B from mytable;
A    B
1    Maus
2   Katze
3   Uhu
4  Pferd
 
I would like to have the following output
A : 1
B : Maus
------
A : 2
B : Katze
etc...
hgraesslin1Asked:
Who is Participating?
 
jocaveConnect With a Mentor Commented:
If you use Tom Kyte's print_table (or some of the subsequent versions), you can do something like

SQL> exec print.tbl( 'select * from emp' );

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> exec print.tbl( 'select * from emp' );
EMPNO                         : 7369
ENAME                         : smith
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17-dec-1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7499
ENAME                         : ALLEN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 20-feb-1981 00:00:00
SAL                           : 1600
COMM                          : 300
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7521
ENAME                         : WARD
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 22-feb-1981 00:00:00
SAL                           : 1250
COMM                          : 500
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7566
ENAME                         : JONES
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 02-apr-1981 00:00:00
SAL                           : 2975
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7654
ENAME                         : MARTIN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 28-sep-1981 00:00:00
SAL                           : 1250
COMM                          : 1400
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7698
ENAME                         : BLAKE
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 01-may-1981 00:00:00
SAL                           : 2850
COMM                          :
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7782
ENAME                         : CLARK
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 09-jun-1981 00:00:00
SAL                           : 2450
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7788
ENAME                         : SCOTT
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 19-apr-1987 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7839
ENAME                         : KING
JOB                           : PRESIDENT
MGR                           :
HIREDATE                      : 17-nov-1981 00:00:00
SAL                           : 5000
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7844
ENAME                         : TURNER
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 08-sep-1981 00:00:00
SAL                           : 1500
COMM                          : 0
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7876
ENAME                         : ADAMS
JOB                           : CLERK
MGR                           : 7788
HIREDATE                      : 23-may-1987 00:00:00
SAL                           : 1110
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7900
ENAME                         : JAMES
JOB                           : CLERK
MGR                           : 7698
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 950
COMM                          :
DEPTNO                        : 30
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7902
ENAME                         : FORD
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 03-dec-1981 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20
COL_FOO                       :
KEY                           :
-----------------
EMPNO                         : 7934
ENAME                         : MILLER
JOB                           : CLERK
MGR                           : 7782
HIREDATE                      : 23-jan-1982 00:00:00
SAL                           : 1300
COMM                          :
DEPTNO                        : 10
COL_FOO                       :
KEY                           :
-----------------

PL/SQL procedure successfully completed.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Try:
Select 'A: ' || A from mytable
Union all
Select 'B: ' || B from mytable
0
 
hgraesslin1Author Commented:
no, sorry. There must be an sql-directive to do that.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ThommyCommented:
select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select rownum,a,b from ((select a,b from test) union all (select a,b from test) order by a))
0
 
slightwv (䄆 Netminder) Commented:
>>  ...(select a,b from test) union all (select a,b from test)...

Not sure what this 'union all' does other than duplicate all the data.

Also using rownum in the outer query as you did gets the rownum there.  I expect you are wanting the rownum from the inner query.  If so, you will need to alias it.
0
 
awking00Commented:
>>how can I achive that sqlplus does not output in columns<<
Any sql query is going to retrieve values in columns, even if it is formed to only retrieve one column. Are there more columns other than A and B in your actual data that you want to display? Perhaps some more detailed examples of the data and the expected output would give us a clearer understanding of your intent.

0
 
ThommyCommented:
@slightwv:

select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select rownum,a,b from ((select a,b from test) union all (select a,b from test) order by a))

The "union all" in the inner query duplicates the rows.

Decode outputs 'A:'||A for odd rownums (e. g. A:1) and 'B:'||B for even rownums (e. g. B:Maus)...

0
 
slightwv (䄆 Netminder) Commented:
@Thommy,

Why duplicate all rows just to get odd/even?  Seems like a lot of work.

Also, are you sure the rownum in the decode works the way you think it does?
0
 
ThommyCommented:
@slightwv:
I want the rownum from the outer query.

Of course the "rownum" of the inner query is unnecessary and can be omitted!!!

select Decode(Mod(rownum,2),1,'A:'||A,'B:'||B) from
(select a,b from ((select a,b from test) union all (select a,b from test) order by a))

Do you have a better idea how to get the desired output with 2 rows from originally one row without duplicating???
0
 
slightwv (䄆 Netminder) Commented:
Guess not.  Even I used a 'union all'.  It just seemed weird initially to return all rows in both (duplicate queries).

The only way I can think to get around it is a pipelined function.
0
 
hgraesslin1Author Commented:
I am still waiting for an sqlplus directive... there must be one....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.