SQL select query with one or two column excluded

Question

how to write a select query with one or two column excluded instead of list down required columns.

for example we have a table Table1 with 10 columns (col_1,col_2,col_2 ..... col_10) and we just want select col_1 to col_8.

instead of writing like

Select col_1, col_2, col_3 .... col_8 from Table1

should we select like this

Select -(col_9, col_10) * from Table1

since you havent named a dbms, i guess you are asking for sql standard. the answer for that is: not possible — x4rf41, Aug 05 '13 at 11:48
you cant do that, its always better to mention which columns are required rather than which are not required — Deepanshu Goyal, Aug 05 '13 at 11:52
Could you try this solution: http://stackoverflow.com/a/6691661/835239 ? — zhongxiao37, Feb 28 '15 at 07:18

score 0 · Answer 1 · answered Aug 05 '13 at 11:52

This is possible but you would have to use dynamic SQL or a stored procedure. It would still mean having to specify the start and end column using fixed column names and indexes and if the columns weren't in running order (colN, colN+1, etc.) then it would become messy.

So I suppose your answer it, just type it out. The benefit you will gain from doing something cleaver is small.

I hope this helps.

Zeigen · Answer 2 · 2013-08-05T12:06:38.587

0

Any ideas?

I think we can select column name first then write into query.

1 Select column with excluded.

SHOW COLUMNS FROM _table WHERE FIELD NOT IN ('column1','column2')

2 Then, Use the column result we've got from above query to write to select query column.

SELECT {result_aboved} FROM _table WHERE 1

but I don't think it can be only once query for this case. you have to query twice times at least.

edited Aug 05 '13 at 12:06

answered Aug 05 '13 at 12:00

Zeigen

126
2
7

Can you turn to process in store procedures? – Zeigen Jul 01 '14 at 09:39

SQL select query with one or two column excluded

2 Answers2