0

How can we vary the number of columns displayed for a 'select' query horizontally (in a horizontal sequence). The sql output displays only 2 columns in a horizontal sequence and shifts the next columns to the next line in groups of two as follows:

NAME | EMP.NO
-----------
DEPT | LOC

I want it to be displayed all in one line as :

NAME |  EMP.NO  | DEPT NO | LOC
William Robertson
  • 15,273
  • 4
  • 38
  • 44
codepoetly
  • 17
  • 1
  • 6
  • first, tell us HOW (share the SQL) and WHERE (version of Oracle DB and the program you are using to run the SQL) you are doing this – thatjeffsmith Jan 02 '18 at 15:14
  • @thatjeffsmith using oracle 11g, its a normal select query on a user defined table, running from the command prompt... – codepoetly Jan 02 '18 at 15:22
  • ok, but what program are you using? SQL*Plus? try set linesize 80 and run your query again -- just a guess since we have no idea what you're really doing – thatjeffsmith Jan 02 '18 at 15:25
  • @thatjeffsmith yes Sql*plus – codepoetly Jan 02 '18 at 15:28
  • Possible duplicate of [How do I format my oracle queries so the columns don't wrap?](https://stackoverflow.com/questions/188118/how-do-i-format-my-oracle-queries-so-the-columns-dont-wrap) – Kaushik Nayak Jan 02 '18 at 15:32
  • yes , that worked, thanks. what is the max length that can be specified? – codepoetly Jan 02 '18 at 15:32
  • ok, so i'm guessing that the col definitions are wider than the data being returned, so the output is wrapping. you need to use the COL command to tell sqlplus how many spaces to reserve for each column or use a more modern tool like SQLcl which has automatic output formatting – thatjeffsmith Jan 02 '18 at 15:33

1 Answers1

0

Well , probably you are looking for this :

1 º 

create table TestTable (id number, name varchar2(10))

insert into TestTable values (1, 'John')
insert into TestTable values (2, 'Mckensy')
insert into TestTable values (3, 'Valneech')
insert into TestTable values (4, 'Zeebra')

2 º 

select 'id', LISTAGG(id, ' ') WITHIN GROUP (ORDER BY name)      
from testtable
union 
select 'name', LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name)
from testtable ;

Actually i dunno which Oracle version are you using.

You can use PIVOT clause , might be helpfull .

Hope it works.

Luiz
  • 141
  • 2
  • 14