SQL get last four year data by comma separated month wise-CodePudding

I have a table that stores, number of fruits sold on each day. Stores number of items sold on particular date.

CREATE TABLE data
(
    [code] [nvarchar](50) NOT NULL,
    [amount] int,
    [date]  [smalldatetime] NOT NULL
);

Sample data:

code	amount	date
aple	10	2010-01-01
aple	32	2010-01-01
aple	20	2010-02-01
aple	30	2010-03-01
aple	40	2010-04-01
aple	10	2011-01-01
aple	20	2011-02-01
aple	30	2011-03-01
aple	40	2011-04-01
aple	10	2012-01-01
aple	20	2012-02-01
aple	30	2012-03-01
aple	40	2012-04-01
aple	10	2013-01-01
aple	22	2013-01-01
aple	20	2013-02-01
aple	30	2013-03-01
aple	40	2013-04-01

I need to write a query to list out the sum of amounts earned each month for the last 4 years as comma separated list.

Expected result:

Year	AmountEarnedInMonthWise
2010	42, 20, 30, 40, 0, 0, 0, 0, 0, 0, 0, 0 (Sum of month wise data as comma separated list)
2011	10, 20, 30, 40, 0, 0, 0, 0, 0, 0, 0, 0
2012	42, 20, 30, 40, 0, 0, 0, 0, 0, 0, 0, 0
2013	32, 20, 30, 40, 0, 0, 0, 0, 0, 0, 0, 0

CodePudding user response：

A combination of PIVOT and CONCAT_WS() is an option:

SELECT
   [Year], 
   AmountEarnedInMonthWise = CONCAT_WS(
      ', ', 
      ISNULL([1], 0), 
      ISNULL([2], 0), 
      ISNULL([3], 0), 
      ISNULL([4], 0), 
      ISNULL([5], 0), 
      ISNULL([6], 0), 
      ISNULL([7], 0), 
      ISNULL([8], 0), 
      ISNULL([9], 0), 
      ISNULL([10], 0), 
      ISNULL([11], 0), 
      ISNULL([12], 0)
   )
FROM (
   SELECT YEAR([date]) AS [Year], MONTH([date]) AS [Month], amount
   FROM Data
) t
PIVOT(
   SUM(amount) 
   FOR [Month] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) p

CodePudding user response：

similarly approach using PIVOT and cte

; with months as 
( select 1 as m
union all
select m 1 as m from months where m<12)
, years as 
(
select min(Year(date)) as y,max(Year(date))  as y1 from data
union all
select y 1 as y,y1  from years where y < y1
), codes as
(
select distinct code as c from data
)
, my as 
(
select y,m,c from months cross join years cross join codes
)
 

select 
y as year, 
c as code, 
concat([1],',',[2],',',[3],',',[4],',',[5],',',[6],',',[7],',',[8],',',[9],',',[10],',',[11],',',[12] )as amount
from 
(
select my.*, isnull(amount,0) as amount 
from data d right join my
on my.m =month(date)
and my.y=year(date)
and my.c=code

) as s
pivot
(
sum(amount) for m in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
)p

demo link