pivots

gistfile1.txt

with basequery as (

select customer_name,
 product_code,
 sum(sales_qty) as sales_qty,
 convert(varchar(2),datediff(month, invoice_date, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) as [age]
 
  from sh_select_trans_view
  where customer_name is not null
	and invoice_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-13, 0) -- rolling 13 month
  group by customer_name,product_code,datediff(month, invoice_date, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
  
 ) 
 select customer_name,
 product_code,[age] as age,[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]
 
 from basequery
 pivot
  (
  sum(sales_qty)
   for [age] in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
   ) as pvt