CTE Example with Pivot operator

,

This T-SQL script contains DDL to create a table and DML to insert a few rows of test data, then a CTE is provided which does some aggregations, while using the Pivot operator to show the data by year and month, along with a new yearly total amount also.

 

CREATE TABLE orders 
 ( order_id  int      identity(1,1) primary key
 , amount    numeric(10,2)
 , order_dt  datetime
) ;


INSERT orders 
( amount, order_dt ) VALUES
 ( 10.01, '4-01-2019' )
,(  9.99, '3-16-2019' )
,(  7.01, '1-15-2019' )
,( 23.46, '4-15-2019' )
,(  3.16, '2-14-2019' )
,(  4.13, '2-11-2019' )
,( 12.14, '7-04-2019' )
,( 13.16, '5-20-2019' )
,(  6.10, '8-23-2019' )
,(  1.53, '1-01-2020' )
,( 40.66, '1-18-2020' )
;

;with cte_amt as
( select [amount], year(order_dt) as [yr], yr_amount, month(order_dt) as [mo]
  from [orders] o
  JOIN ( select year([order_dt]) as [yr], sum([amount]) as [yr_amount] from [orders] group by year([order_dt]) ) as y 
    ON year([order_dt]) = y.[yr]
)
select yr as [Year], [yr_amount] as [Annual Total]
  , coalesce([1] ,0) as  [Jan], coalesce([2] ,0) as  [Feb], coalesce([3] ,0) as  [Mar]
  , coalesce([4] ,0) as  [Apr], coalesce([5] ,0) as  [May], coalesce([6] ,0) as  [Jun]
  , coalesce([7] ,0) as  [Jul], coalesce([8] ,0) as  [Aug], coalesce([9] ,0) as  [Sep]
  , coalesce([10],0) as  [Oct], coalesce([11] ,0) as [Nov], coalesce([12],0) as  [Dec]
from cte_amt
pivot(sum(amount) for mo in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) as pvt
;

Rate

5 (1)

Share

Share

Rate

5 (1)