Oracle - select statement alias one column and wildcard to get all remaining columns

Question

New to SQL. Pardon me if this question is a basic one. Is there a way for me to do this below

SELECT COLUMN1 as CUSTOM_NAME, <wildcard for remaining columns as is> from TABLE;

I only want COLUMN1 appear once in the final result

Answer 1

There is no way to make that kind of dynamic SELECT list with regular SQL*.

This is a good thing. Programming gets more difficult the more dynamic it is. Even the simple * syntax, while useful in many contexts, causes problems in production code. The Oracle SQL grammar is already more complicated than most traditional programming languages, adding a little meta language to describe what the queries return could be a nightmare.

*Well, you could create something using Oracle data cartridge, or DBMS_XMLGEN, or a trick with the PIVOT clause. But each of those solutions would be incredibly complicated and certainly not as simple as just typing the columns.

Answer 2

This is about as close as you will get. It is very handy for putting the important columns up front, while being able to scroll to the others if needed. COLUMN1 will end up being there twice.

SELECT COLUMN1 as CUSTOM_NAME, 
       aliasName.*
FROM TABLE aliasName;

Answer 3

In case you have many columns it might be worth to generate a full column list automatically instead of relying on the * selector.

So a two step approach would be to generate the column list with custom first N columns and unspecified order of the other columns, then use this generated list in your actual select statement.

-- select comma separated column names from table with the first columns being in specified order
select
LISTAGG(column_name, ', ') WITHIN GROUP (
  ORDER BY decode(column_name,
                  'FIRST_COLUMN_NAME', 1,
                  'SECOND_COLUMN_NAME', 2) asc) "Columns"
from user_tab_columns
where table_name = 'TABLE_NAME';

Replace TABLE_NAME, FIRST_COLUMN_NAME and SECOND_COLUMN_NAME by your actual names, adjust the list of explicit columns as needed.

Then execute the query and use the result, which should look like

FIRST_COLUMN_NAME, SECOND_COLUMN_NAME, OTHER_COLUMN_NAMES

Ofcourse this is overhead for 5-ish columns, but if you ever run into a company database with 3 digit number of columns, this can be interesting.