Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Aggregations

KATER__LIST

The field's different values, as one text value.

Family: Aggregations

Syntax 

KATER__LIST(field)

Arguments

ArgumentDescription
fieldThe field to aggregate.

Returns

Text: each different value once, in text order, separated by a comma and a space.

Usage notes

  • Values sort as text, so 10 comes before 9.
  • Empty values are left out. A group with only empty values gives NULL.
  • A list longer than 512 characters is cut short, and ends with an ellipsis (…).

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

SELECT
  orders.region AS "Region",
  KATER__LIST(orders.channel) AS "Channels"
FROM kater_dataset.orders AS orders
GROUP BY
  orders.region
ORDER BY
  1
RegionChannels
AMERemail, paid, store, web
APACemail, paid, store, web
EMEAemail, paid, store, web
LATAMemail, paid, store, web
nullemail, paid, store, web

Compiles to 

CASE
  WHEN LENGTH(
    LISTAGG(
      DISTINCT CAST(orders.channel AS TEXT), ', '
      ORDER BY
        CAST(orders.channel AS TEXT)
    )
  ) > 512
  THEN LEFT(
    LISTAGG(
      DISTINCT CAST(orders.channel AS TEXT), ', '
      ORDER BY
        CAST(orders.channel AS TEXT)
    ),
    511
  ) || '…'
  ELSE LISTAGG(
    DISTINCT CAST(orders.channel AS TEXT), ', '
    ORDER BY
      CAST(orders.channel AS TEXT)
  )
END
CASE
  WHEN LENGTH(
    LISTAGG(DISTINCT CAST(orders.channel AS VARCHAR), ', ') WITHIN GROUP (ORDER BY
      CAST(orders.channel AS VARCHAR))
  ) > 512
  THEN LEFT(
    LISTAGG(DISTINCT CAST(orders.channel AS VARCHAR), ', ') WITHIN GROUP (ORDER BY
      CAST(orders.channel AS VARCHAR)),
    511
  ) || '…'
  ELSE LISTAGG(DISTINCT CAST(orders.channel AS VARCHAR), ', ') WITHIN GROUP (ORDER BY
    CAST(orders.channel AS VARCHAR))
END

See also