Month wise sum + PIVOT in sql server

Question

I have 2 columns amount and date in a table

Amount   |  Date (MM/DD/YYYY)

5         01/01/19
10        02/01/19
10        03/01/19
10        03/21/19
10        04/21/19

Expected result :

01/01    02/01   03/01   04/01
  5        10      20      10

Actually you can do this: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query Just remember about: https://stackoverflow.com/questions/1439403/sql-server-dynamic-pivot-table-sql-injection — MMV, Oct 15 '19 at 11:53
https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query has a set of very good answers — MMV, Oct 15 '19 at 11:54

score 2 · Answer 1 · answered Oct 15 '19 at 10:28

2

If you know the columns you want, you can use conditional aggregation (or pivot):

select sum(case when month(date) = 1 then amount end) as [01/01],
       sum(case when month(date) = 2 then amount end) as [02/01],
       sum(case when month(date) = 3 then amount end) as [03/01],
       sum(case when month(date) = 4 then amount end) as [04/01]
from t
where date >= '2019-01-01' and date < '2020-01-01'

If you want a dynamic set of columns, then you need a dynamic pivot. That cannot be done with a single select.

answered Oct 15 '19 at 10:28

Gordon Linoff

1,242,037
58
646
786

This should be '20190101' and '20200101', or '2019-01-01T00:00:00.000' otherwise you get a syntax error if the server/user/login language isn't English. Well ok, interestingly not for 01-01 but for 01-31. – Stefan Steiger Oct 15 '19 at 11:34
@StefanSteiger . . . To the best of my knowledge, the ISO 8601 standard YYYY-MM-DD works for all internationalization settings except one (a French setting that defaults to YDM). Because it is standard, I prefer it over not having the hyphens. – Gordon Linoff Oct 15 '19 at 12:27
If the language is German, it is interpreted as YYYY-DD-MM. Since DD can be > 12, it throws in those cases. YYYYMMDD works everywhere. Since coincidentially you have DD = MM, it doesn't matter - but only IN THIS CASE - and it's still wrong. Example: SET LANGUAGE German; SELECT CAST('2019-01-01' AS datetime) AS d UNION ALL SELECT CAST('2019-12-31' AS datetime) AS d – Stefan Steiger Oct 15 '19 at 12:52

score 1 · Answer 2 · answered Oct 15 '19 at 11:37

Try Below Query

  select DECODE(to_char(date), 'MM'), '04', sum(amount) ) AS 01/01,
     DECODE(to_char(date), 'MM'), '05', sum(amount) ) AS 02/01,
     DECODE(to_char(date), 'MM'), '06', sum(amount) ) AS 03/01,
     DECODE(to_char(date), 'MM'), '07', sum(amount) ) AS 04/01,
     DECODE(to_char(date), 'MM'), '08', sum(amount) ) AS 05/01,
     DECODE(to_char(date), 'MM'), '09', sum(amount) ) AS 06/01,
     DECODE(to_char(date), 'MM'), '10', sum(amount) ) AS 07/01,
     DECODE(to_char(date), 'MM'), '11', sum(amount) ) AS 08/01,
     DECODE(to_char(date), 'MM'), '12', sum(amount) ) AS 09/01,
     DECODE(to_char(date), 'MM'), '01', sum(amount) ) AS 10/01,
     DECODE(to_char(date), 'MM'), '02', sum(amount) ) AS 11/01,
     DECODE(to_char(date), 'MM'), '03', sum(amount) ) AS 12/01 from t

Month wise sum + PIVOT in sql server

2 Answers2