sqlsnowflake-cloud-data-platformdynamic-pivotrevenue

How to pivot Snowflake table in SQL?


I am trying to show revenue by products and month as a pivot style table. I have dates ranging back to 2020 but this is something I can filter on when selecting. Each month may have multiple revenue activity so I am looking for the sum of that entire month, ie show me entire revenue for month of April.

This is an example of information in the existing table

product date_sold revenue
software 2021-11-13 $ 1000
hardware 2022-02-17 $ 570
labor 2020-04-30 $ 472
hardware 2020-04-15 $ 2350

I'm not very experienced in sql, but I tried google searching and looking over stackoverflow and this is what I'm tinkering with. `

SELECT
    product,
    [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
FROM
(Select 
product,
revenue,
date_trunc('month', date_sold) as month
  from
    fct_final_net_revenue) source
PIVOT
(   SUM(revenue)
    FOR month
    IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
) AS pvtMonth;

This is what I'd like the results to look like

product Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
Software 0 1200 1200 1200 0 0 0 0 0 150 175 300
Labor 0 0 150 2822 150 150 150 150 0 0 0 0
Hardware 0 0 0 0 0 0 0 75 75 75 75 75

Solution

  • Don't use column names such as month; avoiding reserved words / key words, etc, makes code easier to read and avoids errors.

    Then, use MONTH() to get the month without the year part. Don't truncate to a month, that keeps the year.

    The pivot then needs to refer to the values in the column being pivotted. Using [1] implies a column name, however; '1' is a string and 1 is an integer.

    Finally, you can alias the columns from the pivot.

    SELECT
      pvt_month.*
    FROM
    (
      SELECT
        product,
        revenue,
        MONTH(date_sold)   AS month_sold
      FROM
        fct_final_net_revenue
    )
      AS source
    PIVOT
    (
      SUM(revenue)
        FOR month_sold IN (
          1,2,3,4,5,6,7,8,9,10,11,12
        )
    )
      AS pvt_month(
        product, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
      )
    

    https://docs.snowflake.com/en/sql-reference/constructs/pivot.html