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>
sqlite>
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;
AcctNumber|Custname|Addr1|Addr2|City|State|Zipcode|Contact|Phone
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;
AcctNumber~Custname~Addr1~Addr2~City~State~Zipcode~Contact~Phone
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
sqlite>
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;
AcctNumber,Custname,Addr1,Addr2,City,State,Zipcode,Contact,Phone
130169,"Acme Widgets","1744 Alder Road","Apt 31C",Springfield,VA,20171,"Alan All
en",5715551267
sqlite>
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 |