Solved

Query Output, Alphabetical order

Posted on 2013-06-11
4
520 Views
Last Modified: 2013-06-11
Hi,
I have a table with ~50 columns.
When I do a SELECT * FROM <table_name>, I get the data output in the original column order.
I need to get the columns in alphabetical order, is there a way to do it?

Could someone please suggest?


-Thanks
0
Comment
Question by:MRPT
  • 2
4 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 39238447
Just provide the columns:
select a_col, b_col, c_col, ... from table_name;

You can probably come up with some pretty complex SQL that goes against dba_tab_columns but it's likely not worth it.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 333 total points
ID: 39239107
I don't see any easy one step solution for this. If it is not often repeatable, you might try something like in the following example:
SQL> select * from tablename;
      XCOL       ACOL       FCOL       DCOL
---------- ---------- ---------- ----------
         1          2          3          4
         5          6          5          6
         7          8          9          0
SQL> declare
  2  v_sql varchar2(4000) := 'select ';
  3  v_order varchar2(4000);
  4  begin
  5  select listagg(column_name,',') within group (order by column_name) into v_order
  6  from user_tab_columns where table_name = 'TABLENAME';
  7  v_sql := v_sql||v_order||' from tablename';
  8  dbms_output.put_line(v_sql);
  9  end;
 10  /
select ACOL,DCOL,FCOL,XCOL from tablename ==> the paste this to sqlplus screen
SQL> select ACOL,DCOL,FCOL,XCOL from tablename;

      ACOL       DCOL       FCOL       XCOL
---------- ---------- ---------- ----------
         2          4          3          1
         6          6          5          5
         8          0          9          7
0
 
LVL 32

Accepted Solution

by:
awking00 earned 333 total points
ID: 39239115
I forgot to mention the listagg is only available in 11gR2, but stragg and xmlagg can accomplish the same thing and there are plenty of examples of those functions here on EE.
0
 

Author Comment

by:MRPT
ID: 39239300
Thank You.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

863 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

19 Experts available now in Live!

Get 1:1 Help Now