The PIVOT function converts a result set from rows to columns, according to one of the with GROUP BY parameters values.

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.

 

Examples

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:

United States Israel Ireland
11,137 7,470 1,723

 

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:
day United States Israel Ireland
2017-09-20 5,340 4,999 777
2017-09-21 2,554 1,402 355
2017-09-22 3,243 1,069 591
Print Friendly, PDF & Email