CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
user1482689
user1482689

Reputation: 31

Month wise sum + PIVOT in sql server

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

Upvotes: 0

Views: 86

Answers (2)

Kiran Patil
Kiran Patil

Reputation: 339

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 2

Related Questions