Solved

Query Output, Alphabetical order

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

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 31

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

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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

13 Experts available now in Live!

Get 1:1 Help Now