Solved

format sqlplus output

Posted on 2010-11-28
11
1,063 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
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 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 32

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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
data lookup in Oracle - need suggestions 55 113
Consolidating oracle query results to a single line 8 60
SQL Query 34 99
grant user/role question 11 28
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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