Solved

Query Output, Alphabetical order

Posted on 2013-06-11
4
521 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PL/SQL Search for multiple strings 5 53
Oracle - Create Procedure with Paramater 16 63
Web Service from a stored procdure oracle 10 54
Oracle Listener Not Starting 11 44
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

785 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