0

In my database table a lot of columns using 'X_' prefix, anyway I don't to see all those columns having 'X_' as prefix using SELECT query.

Note : I know if we define individual required column names in SELECT query, then we can achieve result set with specified column names. But, I am lazy guy.:)

So, I need SELECT query which excludes all those columns having 'X_' as a prefix.

For Example:

-------------------------------------------------------------
SN    |   NAME  |  X_STATUS  |  X_MODE  |  X_DATE  | ADDRESS
-------------------------------------------------------------
1       Ram         S1           M1                    A
2       Sita        S1           M1                    B
3       Gita        S2           M2                    C
4       Hari        S3           M3                    B

But I need result set like below using 'X_' as a prefix to exclude that prefix having columns.

--------------------------
SN    |   NAME  | ADDRESS
--------------------------
1       Ram         A
2       Sita        B
3       Gita        C
4       Hari        B

Thanks in advance !:)

Bhuwan Prasad Upadhyay
  • 2,916
  • 1
  • 29
  • 33
  • 1
    Which DMBS? You tagged many – Aleksej Oct 19 '16 at 09:07
  • 1
    Which RDBMS you use? You put multiple tags. The solution I'm thinking about is to generate query based on Oracle dictionary but this will work only for Oracle so please define which DB you use. – Kacper Oct 19 '16 at 09:07
  • @Kacper, I prefer oracle. But any rdbms i have no problem. – Bhuwan Prasad Upadhyay Oct 19 '16 at 09:09
  • 1
    In order to get this projection, desired columns have to be included in the select list, there is no other way. The question is whether you do it manually or you'll automate the process dynamically constructing the query using `user_tab_columns` (if you prefer Oracle) dictionary view. – Nick Krasnov Oct 19 '16 at 09:11
  • @Aleksej, Actually i want in oracle db but if this type of query is rdbms dependent then please give me answer i will change my dbms. – Bhuwan Prasad Upadhyay Oct 19 '16 at 09:12
  • The **only** solution is `select sn, name, address from the_table`. There is no such thing as `* except some columns`. Why don't you simply create views that exclude those columns - then you only have to write the column list once. –  Oct 19 '16 at 09:14

1 Answers1

1

I'm not sure if I understand correctly but something like that should help:

select 'select ' || listagg(column_name || ' as X_' || column_name, ', ')  within group(order by COLUMN_ID) || ' 
from T1' from all_tab_columns 
where table_name = 'T1';

Maybe you need to add where column_name like 'X!_%' escape '!' or some other filter for cols you need in select

Kacper
  • 4,798
  • 2
  • 19
  • 34