sqlite howto sort columns?

Question

when I look at my large table in a sqlite file, using an sqlite browser such as Firefox plugin SQLite Manager or the SQLite Browser, I see that the columns are shown according to their sequence of addition to the table (I have been adding new columns in a loop).

A   J   D   B
--- --- --- ---
..  ..  ..  ..

this is a visual annoyance because the table is very large has many many columns, and for the purpose of debugging, I need to visually check neighboring columns. How can I sort the table so that the columns are sorted, like:

A   B   J   D
--- --- --- ---
..  ..  ..  ..

thanks.

score 1 · Accepted Answer · answered Feb 04 '16 at 08:48

1

SQLite has no built-in mechanism to reorder the columns of a table.

You'd have to recreate the table:

CREATE TABLE new_MyTable(A, B, J, D [...]);
INSERT INTO new_MyTable SELECT A, B, J, D FROM MyTable;
DROP TABLE MyTable;
ALTER TABLE new_MyTable RENAME TO MyTable;

answered Feb 04 '16 at 08:48

CL.

173,858
17
217
259

Both answers are great! But I finally used this one. I needed to do a lot string manipulation (with python) in order to be able to first retrieve the long list of names automatically, then sort them. Using valuable hints from [this post](http://stackoverflow.com/a/685212/5177935) and [this post](http://stackoverflow.com/a/7991230/5177935). – xaratustra Feb 05 '16 at 09:02

score 1 · Answer 2 · answered Feb 04 '16 at 09:26

You could create a DebugView with the columns reordered as you please...

CREATE TABLE ChorleyHandford (IMAGE_REF, DATE, LOCATION, NUMBER);
INSERT INTO "ChorleyHandford" VALUES('A10006','16/10/1990','RATCLIFFE POWER STATION CASTLE DONNINGTON POWER STATION','27');
INSERT INTO "ChorleyHandford" VALUES('A10017','25/10/1990','BROMLEY PARKS','17');
INSERT INTO "ChorleyHandford" VALUES('A10018','25/10/1990','CROSSWAYS BUSINESS PARK DARTFORD CROSSING BRIDGE CONSTRUCTION','20');
INSERT INTO "ChorleyHandford" VALUES('A10022','25/10/1990','THURROCK LAKESIDE','11');
INSERT INTO "ChorleyHandford" VALUES('A10057','01/11/1990','CLANDON REGIS SITE','6');

See what you have:

select * from CHORLEYHANDFORD;

A10006|16/10/1990|RATCLIFFE POWER STATION CASTLE DONNINGTON POWER STATION|27
A10017|25/10/1990|BROMLEY PARKS|17
A10018|25/10/1990|CROSSWAYS BUSINESS PARK DARTFORD CROSSING BRIDGE CONSTRUCTION|20
A10022|25/10/1990|THURROCK LAKESIDE|11
A10057|01/11/1990|CLANDON REGIS SITE|6

Create a reordered view and see how it looks now:

create view dbgview as select NUMBER, LOCATION, DATE, IMAGE_REF FROM CHORLEYHANDFORD;
select * from dbgview;

27|RATCLIFFE POWER STATION CASTLE DONNINGTON POWER STATION|16/10/1990|A10006
17|BROMLEY PARKS|25/10/1990|A10017
20|CROSSWAYS BUSINESS PARK DARTFORD CROSSING BRIDGE CONSTRUCTION|25/10/1990|A10018
11|THURROCK LAKESIDE|25/10/1990|A10022
6|CLANDON REGIS SITE|01/11/1990|A10057

sqlite howto sort columns?

2 Answers2