27

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?

  • why don't we just allow HTML tables? I don't understand everyone's hatred of HTML tables.. with HTML tables, you can easily right click export to Excel. – Aaron Kempf Oct 31 '12 at 23:09
  • 3
    @AaronKempf I'd prefer markdown tables myself – Jack Douglas Nov 1 '12 at 5:58
31

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:

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.

  • I'd actually always wondered about this myself! :D – Aarthi Oct 31 '12 at 15:36
  • 15
    @Aarthi would a feature-request for markdown tables just for dba.se be rejected out of hand do you think? I'm aware there is one on mSO but we are dealing with tabular data in a higher proportion of answers – Jack Douglas Oct 31 '12 at 16:15
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  │
└───╨───────────────────┘
  • 1
    expanded display is awesome for display a row (or a small number of rows) with lots of columns – Jack Douglas Dec 30 '16 at 10:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .