On this page

    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.