Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Output, Alphabetical order

Posted on 2013-06-11
4
Medium Priority
?
527 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

604 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