Categories: Aggregate

PIVOT

Converts a set of data from rows into columns

Syntax

PIVOT(pivot_clause expression, pivot_for_clause expression, pivot_in_clause expression) → set of columns

  • pivot_clause: The query to aggregate the data on
  • pivot_for_clause: Which columns to group and pivot on
  • pivot_in_clause: Filters the values for the columns pivot_for_clause. Each of the values in this clause will be a separate column.

Examples

ALTER DATASET Samples."samples.dremio.com"."SF weather 2018-2019.csv" REFRESH METADATA auto promotion FORCE UPDATE;

SELECT * FROM (
    SELECT  EXTRACT(YEAR FROM CAST(F AS DATE)) as "YEAR",
            EXTRACT(MONTH FROM CAST(F AS DATE)) as "MONTH",
            K as MAX_TEMP
    FROM Samples."samples.dremio.com"."SF weather 2018-2019.csv"
    where F <> 'DATE'
)
PIVOT (
    max(MAX_TEMP) for "MONTH" in (1 as JAN, 2 as FEB, 3 as MAR, 4 as APR, 5 as MAY, 6 as JUN, 7 as JUL, 8 as AUG, 9 as SEP, 10 as OCT, 11 as NOV, 12 as "DEC")
);

-- YEAR, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
-- 2018, 67, 77, 81, 74, 77, 80, 77, 72, 83, 81, 81, 62
-- 2019, 66, 62, 73, 82, 62, NULL, NULL, NULL, NULL, NULL, NULL, NULL

Usage Notes

This keyword is applied to a SELECT statement.