Categories: Aggregate

UNPIVOT

Converts a set of data from columns into rows

Syntax

UNPIVOT(unpivot_clause expression, unpivot_for_clause expression, unpivot_in_clause expression) → set of rows

  • unpivot_clause: Name of the column for each of the column values from the pivoted data
  • unpivot_for_clause: Name of the column for the number values shown in the pivoted data
  • unpivot_in_clause: List of pivoted columns

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")
)
UNPIVOT (
    GLOBAL_MAX_TEMP for "MONTH" in (JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, "DEC")
)
ORDER BY "YEAR", "MONTH";

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

Usage Notes

This keyword is applied to a SELECT statement.