Doing something with a query result

Demonstrating different output methods.

SQLite is capable of formating it's output in 8 different ways using the .mode command. In HTML mode the rows returned are formatted as HTML table rows. The only thing lacking in the table markup is the <TABLE> tag at the start and the </TABLE> at the end.

sqlite> .mode html
sqlite> .headers ON
sqlite> select * from Customers limit 1;
<TR> <TH>AcctNumber</TH> <TH>Custname</TH> <TH>Addr1</TH> <TH>Addr2</TH> <TH>City</TH> <TH>State</TH> <TH>Zipcode</TH><TH>Contact</TH><TH>Phone</TH></TR>
<TR><TD>130169</TD> <TD>Acme Widgets</TD> <TD>1744 Alder Road</TD><TD>Apt 31C</TD> <TD>Springfield</TD><TD>VA</TD><TD>20171</TD><TD>Alan Allen</TD><TD>5715551267</TD></TR>

In list mode values for each field in the record are delimited by pipes "| ", unless the delimiter is changed.

sqlite> .mode list
sqlite> select * from Customers limit 1;
130169|Acme Widgets|1744 Alder Road|Apt 31C|Springfield|VA|20171|Alan Allen|5715 551267

using the .separator command you can change the delimiter to whatever character or characters that you desire.

sqlite> .separator ~
sqlite> select * from Customers limit 1;
130169~Acme Widgets~1744 Alder Road~Apt 31C~Springfield~VA~20171~Alan Allen~5715 551267
sqlite> .separator _-_
sqlite> select * from Customers limit 1;
130169_-_Acme Widgets_-_1744 Alder Road_-_Apt 31C_-_Springfield_-_VA_-_20171_-_Alan Allen_-_5715551267

In the Comma separated value or CSV mode, all non numeric values are enclosed in quotation marks and values are delimited by commas. This format is recognized by a number of database and spreadsheet programs.

sqlite>.mode csv
sqlite> select * from Customers limit 1;
130169,"Acme Widgets","1744 Alder Road","Apt 31C",Springfield,VA,20171,"Alan All en",5715551267

The column format is often the neatest in terms of display, but it often requires trial and error tweeking of the column width with the ".width" command to get it right.

sqlite> .mode column
sqlite> .width 12 20 12 5
sqlite> select AcctNumber,Custname,City,State from Customers limit 1;

AcctNumber Custname City State
------------ -------------------- ------------ -----
130169 Acme Widgets Springfield VA

Output Query results to a text file in column format

Using the .output command, query results can be redirected into a separate text file instead of the screen. The .output stdout command needs to be used to output to the screen after you are finished.

sqlite> .headers ON
sqlite> .mode columns
sqlite> .width 12 30 10 12
sqlite> .output C:/Databases/Report_1.txt
sqlite> SELECT 'Page 1' '' ,'List of Customers and Invoices' 'Report 1 Test 2';
sqlite> SELECT Customers.AcctNumber 'Acct Number',Customers.Custname 'Company' ,catalogsales.InvoiceNo 'Invoice #' ,SUM(Price*Quan) 'Invoice Amt' FROM Customers LEFT OUTER JOIN catalogsales ON Customers.AcctNumber = catalogsales.AcctNumber GROUP BY Customers.AcctNumber ,catalogsales.InvoiceNo;
sqlite> .output stdout

If no path is specified with the file name , then the output file will be placed in the same directory as the SQLite program.

Report 1 Test 2
------------ ------------------------------
Page 1 List of Customers and Invoices
Acct Number Company Invoice # Invoice Amt
------------ ------------------------------ ---------- ------------
130169 Acme Widgets
130208 Nike Missiles Inc 21001 775.62
130247 Charlies Bakery 21023 892.55
130286 Unisales Inc. 21015 1720.08
130286 Unisales Inc. 21027 1305.34
130325 M.I. Sinform & Sons
130364 Big Dents Towing Inc.
130365 Weneverpay Inc

Note the statement which acts as a header for the report, SELECT 'Page 1' '' ,'List of Customers and Invoices' 'Report 1 Test 2'; .
In column mode the resulting rows are printed below the header. In this particular case the resulting row is
Page 1       List of Customers and Invoices
and the headers are the aliases that follow each field, empty quotes to create a blank space and the title "Report1 Test 2"

Aligning numeric values in a column

Concatenate a string of ten spaces in length with the rounded value of the column and use SUBSTR to to align the values in the column.

sqlite> SELECT amount AS 'raw value', (ROUND(amount,2)) AS 'RND value' , CASE WHEN (LENGTH(ROUND(amount,2))) - (LENGTH(CAST(amount AS INTEGER)) ) =2 THEN SUBSTR('          '||(ROUND(amount,2))||'0', -10,10) ELSE SUBSTR('          '||(ROUND(amount,2 )),-10,10) END AS 'result' FROM CurrencyTest;

raw value RND value result
----------  ----------  ----------
1.0 1.0       1.00
1.1 1.1       1.10
1.021 1.02       1.02
1.01 1.01       1.01
100.2 100.2     100.20
25.257 25.26      25.26
0.586 0.59       0.59
299.9999 300     300.00
53.0 53.0      53.00
35000.12 35000.12   35000.12