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

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

CodePudding user response：

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