How to get the YearNum as columns and total, group by MonthNum from below table

Question

From the above table, how I've to get the total for each year in different columns group by monthnum, I mean MonthNum, TotalIn2013,TotalIn2014,TotalIn2015

Output should be like second table:

Just add a sample for your expected outout for better understanding — Ullas, May 12 '16 at 11:59
PIVOT? https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Serg, May 12 '16 at 12:05

score 0 · Answer 1 · answered May 12 '16 at 12:05

0

select id,monthnum, case when (yearnum = 2013) then sum(total) as [totalIn2013]
when (yearnum = 2014) then sum(total) as [totalIn2014] 
when (yearnum = 2015) then sum(total) as [totalIn2015] from tablename
end
group by id,monthnum

answered May 12 '16 at 12:05

Bishoy Frank

114
1
7

score 0 · Answer 2 · answered May 12 '16 at 12:10

Consider this from your sample:

DECLARE @t TABLE 
(
  YearNum INT 
, MonthNum INT
, Total BIGINT
)

INSERT INTO @t (YearNum, MonthNum, Total)
VALUES (2013, 11, 15100)
, (2014, 11, 117100)
, (2014, 1, 64000)
, (2014, 2, 59600)
, (2015, 1, 224150)
, (2015, 2, 191660);

Use the PIVOT Function to achieve what you need:

SELECT *
FROM @t
PIVOT (
    SUM(Total)
    FOR YearNum IN ([2013], [2014], [2015]) 
) p
ORDER BY MonthNum

If the years you want to display are dynamic, you would need to come up with dynamic sql, recursive CTE or some smart apply.

score 0 · Answer 3 · answered May 12 '16 at 12:14

0

Use MS SQL PIVOT technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx It' quite enough if every month has at least one row for at least one year. Otherwise you may need to PIVOT a LEFT JOIN of inline tally table for months to your data.

answered May 12 '16 at 12:14

Serg

22,285
5
21
48

score 0 · Answer 4 · answered May 12 '16 at 12:15

 SELECT Id,
        MonthNum, 
        CASE WHEN (YearNum = 2013) THEN SUM(Total) END AS Total2013
        CASE WHEN (YearNum = 2014) THEN SUM(Total) END AS Total2014
        CASE WHEN (YearNum = 2015) THEN SUM(Total) END AS Total2015
 FROM TABLE      
 GROUP BY Id, MonthNum

score 0 · Answer 5 · answered May 12 '16 at 12:22

You can achieve this with a dynamic sql query also.

Query

declare @sql as varchar(max);

select @sql = 'select MonthNum,' 
        + stuff(
            (
               select distinct ',sum(case YearNum when ' 
               + cast(Yearnum as varchar(4)) 
               + ' then Total else 0 end) as TotalIn' 
               + cast(Yearnum as varchar(4))
    from your_table_name
    for xml path('')
), 1,1, '');

select @sql += ' from table_1 group by MonthNum;';

exec(@sql);

How to get the YearNum as columns and total, group by MonthNum from below table

5 Answers5