Skip to main content

Define different titles with LABEL

Note

This page is about the TABULATE usage of the LABEL clause. For the VISUALISE usage, see this page.

The LABEL clause is a straightforward clause allowing you to override the default naming of column labels, spans, (sub)titles and captions. Unlike VISUALISE’s usage, there is no automatic labelling: a column with no label keeps its name as header.

Clause syntax

The LABEL clause requires a comma-separated list of key-value pairs

LABEL <column/title> => <string/null>, ...

On the left hand side of the arrow, the following keys can be used:

  • Column names as they occur in the data
  • Reserved keywords for special labels:
    • title as the main title
    • subtitle as the subtitle
    • caption as the table caption
  • Spanner IDs declared by SPAN clauses.

On the right hand side of the arrow, one can use a string to give new label, or null to hide the label.

Rich text

The support of rich text in tables is planned but not yet implemented.

Examples

Unlike using aliases in TABULATE, the LABEL clause lets you change column labels without changing column order

CREATE TEMP TABLE my_data AS SELECT * FROM ggsql:penguins LIMIT 5

TABULATE * FROM my_data
  LABEL bill_len => 'Bill length', bill_dep => 'Bill depth'
speciesislandBill lengthBill depthflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

Hiding labels.

TABULATE * FROM my_data
  LABEL island => null, species => null
bill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

Adding titles to a table.

TABULATE * FROM my_data
  LABEL
    title => 'Penguins!',
    subtitle => 'Structural size measurements',
    caption => 'Source: Palmer station, Antarctica'
Source: Palmer station, Antarctica
Penguins!
Structural size measurements
speciesislandbill_lenbill_depflipper_lenbody_masssexyear
AdelieTorgersen39.118.71813750male2007
AdelieTorgersen39.517.41863800female2007
AdelieTorgersen40.3181953250female2007
AdelieTorgersennullnullnullnullnull2007
AdelieTorgersen36.719.31933450female2007

When column names and reserved keywords compete for a label, the reserved keyword wins.

-- data has column named 'title'
CREATE TEMP TABLE title_data AS 
SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t(id, title)

TABULATE * FROM title_data
  LABEL
    title => 'Overrules column'
Overrules column
idtitle
1a
2b
3c

You can target an aliased column instead.

TABULATE *, title AS title_col FROM title_data
  LABEL
    title => 'Main title',
    title_col => 'Column title'
Main title
idColumn title
1a
2b
3c