Select * with Case statement

Question

I have a table that has over 50 columns and I'm trying to select all of them with the exception of one column I want to add a case statement to it, I tried doing

Select A.*,Case when COLUMN1 THEN end as XX from TABLE A,

But this will result in COLUMN1 repeated twice, in the * result and in the case condition, is there a way to exclude this column from the select * results? or do I have to type each each column name and add the case statement to that specific column?

Your help is much appreciated thank you.

There is no way to exclude a column using a simple `SELECT`. — Gordon Linoff, Apr 10 '18 at 17:38
Yes, using Oracle 18c PTF you could achieve it [SELECT * EXCEPT](https://stackoverflow.com/a/49015504/5070879) and example [`skip by name`](https://livesql.oracle.com/apex/livesql/file/content_GA5YI13SCZDGFRUTZYVGS8GO8.html) — Lukasz Szozda, Apr 10 '18 at 17:39
@lad2025 only by first writing a polymorphic table function, which is surely a nontrivial step for someone wanting to write an ad hoc query that skips one column. It appears that an actual `select * except` syntax remains a distant dream. — William Robertson, Apr 10 '18 at 18:02
@WilliamRobertson Not so distant [BigQuery SELECT * EXCEPT](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select-except). Unfortunately there is no direct equivalent for Oracle — Lukasz Szozda, Apr 10 '18 at 19:25
Does this answer your question? [Exclude a column using SELECT \* \[except columnA\] FROM tableA?](/q/729197/90527) — outis, Sep 10 '22 at 21:28

score 0 · Answer 1 · answered Apr 10 '18 at 18:29

As the comments pointed out, you can't easily alter one column in a select * statement. But if you don't want to type out all the column names, you can still use a simple query to grab them for you.

select listagg(column_name, ', ') within group (order by column_id) 
  from user_tab_cols where table_name = 'MY_TABLE';

Some IDEs will also do column name expansion - in SQL Developer, for instance, you can drag a table to the worksheet to have all the columns listed.

Select * with Case statement

1 Answers1