Query all columns but one?

(1) By anonymous on 2024-05-30 18:09:07 [link] [source]

How to create a query that returns all the columns but a specific one?

The following query works:

SELECT GROUP_CONCAT(name,',') FROM PRAGMA_TABLE_INFO('table_name') WHERE name!='excluded_column'

it returns a list of all the columns without 'excluded_column'. But querying from this list somehow, fails:

SELECT (SELECT GROUP_CONCAT(name,',') FROM PRAGMA_TABLE_INFO('table_name') WHERE name!='excluded_column') FROM table_name

is it possible in sqlite?

(2) By SeverKetor on 2024-05-30 19:12:13 in reply to 1 [link] [source]

There is no special construct for "all but n columns".

Your second query is literally just asking for a list of column names for each row in the table.

I say you have three choices: just list the column names you want, do one query to get the names and build a second based on them, or use the exec extension

(3) By anonymous on 2024-05-30 19:41:50 in reply to 2 [link] [source]

ok thank you.

(4.2) By Aask (AAsk1902) on 2024-05-30 21:21:28 edited from 4.1 in reply to 1 [source]

You have not said whether you are using Windows or whether you are using the CLI; however, if you are, try:

.param init
.param set :exclude 1
.param set :table employees
.headers off
.once |clip
select 'select ' || group_concat(replace('[#]','#', name),',') || ' from '|| :table ||';' as sel from pragma_table_info(:table) where cid !=:exclude;
.headers on
.read "|powershell -c get-clipboard"
.param clear

In my example, I am using chinook.db and selecting all columns except LastName (ordinal position 1) from table employees.

the column to be excluded is specified by its ordinal position in the table; ordinal positions are zero-based.
change the following two lines as appropriate

.param set :exclude 1
.param set :table employees