How do I include tables with Ascii art lines in my answer?

Question

Some answers on the site like this one and this one include tables drawn with ascii art. Is this done by hand or is there a tool I can use to help me?

Answer 1

If you have tab-delimited text you can use this web site tool to convert it into a table, either in 'MySQL' or 'Unicode Art' style, eg:

+-------+-------+
| LEVEL | DUMMY |
+-------+-------+
|     1 | X     |
|     2 | X     |
|     3 | X     |
+-------+-------+

╔═══════╦═══════╗
║ LEVEL ║ DUMMY ║
╠═══════╬═══════╣
║     1 ║ X     ║
║     2 ║ X     ║
║     3 ║ X     ║
╚═══════╩═══════╝

Screen grab of site:

It was created by another Stack Exchange user Senseful.

If your table just fails to fit without scrollbars, you may want to make the text smaller by using <pre><sub></sub></pre> rather than indentation for the code block:

╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗
║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║
╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣
║        1 ║ CLUSTERED INDEX ║ IN_ROW_DATA          ║           0 ║ 50.3953545836422               ║
║        1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA    ║           0 ║ 74.3019520632567               ║
║        1 ║ CLUSTERED INDEX ║ LOB_DATA             ║           0 ║ 99.0239683716333               ║
╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝

You can even take it further but <sub> isn't really a substitute for <small> and the vertical spacing is perhaps getting annoying by this stage (it's also getting a little hard to read):

╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗
║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║
╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣
║        1 ║ CLUSTERED INDEX ║ IN_ROW_DATA          ║           0 ║ 50.3953545836422               ║
║        1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA    ║           0 ║ 74.3019520632567               ║
║        1 ║ CLUSTERED INDEX ║ LOB_DATA             ║           0 ║ 99.0239683716333               ║
╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝

Perhaps not so bad with 'MySQL' style:

+-------+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+
| LEVEL | DUMMY | DUMMY_1 | DUMMY_2 | DUMMY_3 | DUMMY_4 | DUMMY_5 | DUMMY_6 | DUMMY_7 | DUMMY_8 | DUMMY_9 | DUMMY_10 | DUMMY_11 | DUMMY_12 |
+-------+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+
|     1 | X     | X       | X       | X       | X       | X       | X       | X       | X       | X       | X        | X        | X        |
|     2 | X     | X       | X       | X       | X       | X       | X       | X       | X       | X       | X        | X        | X        |
|     3 | X     | X       | X       | X       | X       | X       | X       | X       | X       | X       | X        | X        | X        |
+-------+-------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+

Finally, if you are using SQLFiddle, choose 'Markdown' from the 'Run SQL' dropdown and you get something like this:

[SQL Fiddle][1]

**Oracle 11g R2 Schema Setup**:



**Query 1**:

    select level, dummy from dual connect by level<4

**[Results][2]**:

    | LEVEL | DUMMY |
    -----------------
    |     1 |     X |
    |     2 |     X |
    |     3 |     X |



  [1]: http://sqlfiddle.com/#!4/d41d8/3652

  [2]: http://sqlfiddle.com/#!4/d41d8/3652/0

which looks like this if you paste it straight into an answer:

---

SQL Fiddle

Oracle 11g R2 Schema Setup:

Query 1:

select level, dummy from dual connect by level<4

Results:

| LEVEL | DUMMY |
-----------------
|     1 |     X |
|     2 |     X |
|     3 |     X |

---

Another useful tool for generating tables is available at https://ozh.github.io/ascii-tables/ - just a simple copy-paste of the output from an SSMS query results grid will cause an ASCII table to be generated with headers.

Answer 2

PostgreSQL

PostgreSQL's official client, psql, makes this task super easy. It's a command line client.

Further formatting options are available,

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager|
                         unicode_border_linestyle|unicode_column_linestyle|unicode_header_linestyle})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

in the below examples TABLE myTable; is merely a shorthand for SELECT * FROM myTable;

So for instance, a quick display of \pset border

test=# \pset border
Border style is 1.  (this is the default)
test=# TABLE foobar;
 x |         y         
---+-------------------
 1 | -5.53865572996438
 2 | -7.67541305348277
 3 | 0.195378532633185
 4 |  3.72031048033386
 5 |  1.67127958964556
(5 rows)

test=# \pset border 0
Border style is 0.
test=# TABLE foobar;
x         y         
- -----------------
1 -5.53865572996438
2 -7.67541305348277
3 0.195378532633185
4  3.72031048033386
5  1.67127958964556
(5 rows)

test=# \pset border 2
Border style is 2.
test=# TABLE foobar;
+---+-------------------+
| x |         y         |
+---+-------------------+
| 1 | -5.53865572996438 |
| 2 | -7.67541305348277 |
| 3 | 0.195378532633185 |
| 4 |  3.72031048033386 |
| 5 |  1.67127958964556 |
+---+-------------------+
(5 rows)

Other unicode options are kind of cool too.

test=# \pset linestyle unicode
Line style is unicode.
test=# TABLE foobar;
┌───┬───────────────────┐
│ x │         y         │
├───┼───────────────────┤
│ 1 │ -5.53865572996438 │
│ 2 │ -7.67541305348277 │
│ 3 │ 0.195378532633185 │
│ 4 │  3.72031048033386 │
│ 5 │  1.67127958964556 │
└───┴───────────────────┘
(5 rows)


test=# \pset unicode_column_linestyle double
Unicode column line style is "double".
test=# \pset unicode_header_linestyle double
Unicode header line style is "double".
test=# \pset title 'My Foobar'
Title is "My Foobar".
test=# TABLE foobar;
        My Foobar
┌───╥───────────────────┐
│ x ║         y         │
╞═══╬═══════════════════╡
│ 1 ║ -5.53865572996438 │
│ 2 ║ -7.67541305348277 │
│ 3 ║ 0.195378532633185 │
│ 4 ║  3.72031048033386 │
│ 5 ║  1.67127958964556 │
└───╨───────────────────┘
(5 rows)

Some other notes

• footer is corresponds to (5 rows) in the above example
• The expanded display \pset expanded on abbreviated \x is something entirely different. I'm not sure who uses it, or why. It's a horrible display (imho).

Expanded display,

test=# TABLE foobar;
My Foobar
┌─[ RECORD 1 ]──────────┐
│ x ║ 1                 │
│ y ║ -5.53865572996438 │
╞═[ RECORD 2 ]══════════╡
│ x ║ 2                 │
│ y ║ -7.67541305348277 │
╞═[ RECORD 3 ]══════════╡
│ x ║ 3                 │
│ y ║ 0.195378532633185 │
╞═[ RECORD 4 ]══════════╡
│ x ║ 4                 │
│ y ║ 3.72031048033386  │
╞═[ RECORD 5 ]══════════╡
│ x ║ 5                 │
│ y ║ 1.67127958964556  │
└───╨───────────────────┘