On this page

    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.