SQLTeam.com | Weblogs | Forums

Pivot


#1

I have a table with fields that have Year and Month. Also holds an amount.

Looks like this:

Account------Year------Month------Amt
1234 --------2016--------12 ----------120.32
1234 --------2016-------12------------199.00
1234 --------2016-------11------------1998.00

I would like to sum the amount by Year and Month and have my columns be from the current date back 13 periods.

Is that possible?

Looks something like this:

Account------201612 ------- 201611 -------.........
1234-----------319.32----------1998.00


#2

here you are:-

declare @pivot table (Account float, year int, month int, Amt decimal(8,2))

insert into @pivot values
(1234,2016,12,120.32),
(1234,2016,12,199.00),
(1234,2016,11,1998.00)

--Your Original Table
select * from @pivot

--Pivot Table
Select
Account
,[201612]
,[201611]
from (
Select Account,Amt,convert(varchar(4),year)+convert(varchar(2),Month) as Period from @pivot
)main
pivot(sum(Amt) for Period in ([201612],[201611]))t