MY table name product1
ID Amount Date
----
1 300 02-02-2010 00:00
2 400 02-02-2009 00:00
3 200 02-02-2011 00:00
4 300 22-02-2010 00:00
5 400 12-02-2009 00:00
6 500 22-02-2009 00:00
7 600 02-02-2006 00:00
8 700 02-07-2012 00:00
9 500 08-02-2012 00:00
10 800 09-02-2011 00:00
11 500 06-02-2010 00:00
12 600 01-02-2011 00:00
13 300 02-02-2019 00:00
Desired output:
Y1 Y2 Y3 ...........
sum(amount) sum(amount) sum(amount)
What is an approach, where Y1 is the year part of the date, such that the result column would be the following?
YEAR 2006 2009 2010 2011 2012
- ---------
Total 600 1300 800 1900 1200
Create procedure [dbo].[pivot]
as
begin
DECLARE @Years nvarchar(max)
SELECT @Years = STUFF(
(
select distinct ',[' + cast(Year([Dates]) as nvarchar(4)) + ']'
from Product1
for xml path('')
),
1,1,'')
DECLARE @SQL nvarchar(max)
SELECT @SQL = N'
select * from ( select amount, year([Dates]) as [y] from product1 ) Data
PIVOT ( sum(Amount) FOR [y] IN ( ' + @Years + ' ) ) PivotTable
UNION
select * from ( select units, year([Dates]) as [y] from product1 ) Data
PIVOT ( sum(units) FOR [y] IN ( ' + @Years + ' ) ) PivotTable
'
EXECUTE (@SQL)
end