Solved

format sqlplus output

Posted on 2010-11-28
11
1,056 Views
Last Modified: 2012-06-27
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...
0
Comment
Question by:hgraesslin1
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34227042
Try:
Select 'A: ' || A from mytable
Union all
Select 'B: ' || B from mytable
0
 

Author Comment

by:hgraesslin1
ID: 34228128
no, sorry. There must be an sql-directive to do that.
0
 
LVL 19

Expert Comment

by:Thommy
ID: 34229963
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34229991
>>  ...(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
 
LVL 31

Expert Comment

by:awking00
ID: 34230046
>>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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Accepted Solution

by:
jocave earned 250 total points
ID: 34230542
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
 
LVL 19

Expert Comment

by:Thommy
ID: 34230715
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34231293
@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
 
LVL 19

Expert Comment

by:Thommy
ID: 34238944
@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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34239101
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
 

Author Closing Comment

by:hgraesslin1
ID: 34280430
I am still waiting for an sqlplus directive... there must be one....
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now