Syntax

PIVOT <aggregate_function>(<measure>) FOR <dimension> [HIGHEST <n> | IN (<values list>)]

• PIVOT:
  • PIVOT function should be added after GROUP BY / HAVING, and before ORDER BY / LIMIT.
  • Can only use one pivot function per query
  • Can only be used in the top query
  • Cannot be used with Union in the top query (in inner queries does)
  • Cannot be used in queries from Linked Data Sources.
  • Cannot be used with "SELECT * FROM cooladata".
  • Can be used with ORDER BY with any dimension column, or column numbers (1, 2, …) – up to the number of columns in the original query.
• aggregate_function:
  • Supports all basic aggregation functions: SUM, COUNT, MAX, MIN, etc.
  • Can only use one aggregate function.
  • Cannot be used with window functions.
• measure:
  • The measure can be any column (or "*" in case of count function) in the data source.
  • Can only use one measure.
• dimension:
  • The dimension can be any column in the data source.
  • This column will not be included in the result output, but instead, each value will be separated to a column. The column name will the be the value.
  • The pivot dimension should also be included in the GROUP BY clause of the top query (same as the query without pivot).
• HIGHEST <n>:
  • Optional – define the max number of value to return as columns.
  • n can be any number between 1-200.
  • Will return the top column according to the total value of the pivot aggregation function and measure.
  • When omitted, the query returns the top 200 values by default.
  • Cannot be used with "IN" parameter.
• IN (<values list>):
  • Optional – define the exact list of values to return as columns.
  • Values without results will not return as a column.
  • Other values will be ignored.
  • Cannot be used with "HIGHEST" parameter.

Example: Basic query

SELECT ip_country, count(*) AS views
FROM cooladata 
WHERE date_range(context) AND filters(context) 
GROUP BY ip_country 
PIVOT SUM(views) FOR ip_country 
LIMIT 100

Results:

Example: Using IN and multiple breakdown dimensions

SELECT day, ip_country, count(*) as views
FROM cooladata
WHERE date_range(context) AND filters(context) 
GROUP BY day, ip_country
PIVOT SUM(views) FOR ip_country IN ("Israel", "United States", "Ireland")
ORDER BY day
LIMIT 100
Results: