SQLite has several modes and settings that enable you to output query results as a comma-separated list.

For example, you can use the .separator dot command to specify a comma as the separator. Or you could use the “quote” mode to format the output as string literals.

However, for the purposes of this article, I’m going to use csv mode, which formats the results as a comma-separated list, as well as with double quotes around strings. Internal double quotes are double-quoted.

Example

In this example, I switch to csv mode, then select data from the database.

.mode csv
SELECT * FROM Products;

Result:

1,"Widget Holder",139.5
2,"Widget Opener",89.7
3,"Bob's ""Best"" Widget",374.2
4,"Blue Widget",63.0

Note that the internal double quotes were double-quoted, but the internal single quote wasn’t.

If you want to see this compared to the SQLite default mode (pipe-separated list), here it is using that mode.

.mode list
SELECT * FROM Products;

Result:

1|Widget Holder|139.5
2|Widget Opener|89.7
3|Bob's "Best" Widget|374.2
4|Blue Widget|63.0

Add Column Headers

You can also add column headers (column names) by using .headers on.

.headers on
.mode csv
SELECT * FROM Products;

Result:

ProductId,ProductName,Price
1,"Widget Holder",139.5
2,"Widget Opener",89.7
3,"Bob's ""Best"" Widget",374.2
4,"Blue Widget",63.0

If you want to remove headers, you can use .headers off.

Save your Settings

Setting the mode like this only sets it for the current session. If you open a new connection to SQLite, it will revert back to the default settings.

If you want your settings to persist, so that you don’t have to keep changing the mode each time you connect to SQLite, you can store your settings in a .sqliterc file.

To do this, enter the following into a blank text file:

.headers on
.mode csv

Then save that as .sqliterc in your home directory.

Now when you use the SQLite command line shell, it will first check your .sqliterc file for any settings.

The GROUP_CONCAT() Function

You can alternatively use the Group_Concat() function to convert query results to a comma-separated list from within a SQL query itself.