?
Solved

Query Output, Alphabetical order

Posted on 2013-06-11
4
Medium Priority
?
526 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 501 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 999 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 999 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

777 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