1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
| create table #test
(code_option varchar(10)
,agent char(2)
,exercice int
,unite int
)
insert into #test values ('op100','a1','2019','5')
insert into #test values ('op101','a2','2020','15')
insert into #test values ('op102','a3','2018','10')
insert into #test values ('op103','a2','2016','8')
insert into #test values ('op104','a4','2017','5')
insert into #test values ('op105','a1','2019','10')
insert into #test values ('op107','a3','2019','5')
insert into #test values ('op107','a4','2016','10')
insert into #test values ('op108','a3','2015','20')
insert into #test values ('op109','a2','2020','5')
insert into #test values ('op110','a1','2018','6')
with cte
as(
SELECT distinct(agent),
[2020], [2019], [2018], [2017], [2016] , [2015]
FROM
(SELECT distinct(agent),exercice,unite
FROM #test ) AS SourceTable
PIVOT
(
SUm(unite)
FOR exercice IN ([2020], [2019], [2018], [2017], [2016] , [2015] )
) AS PivotTable
) select distinct(agent), isnull([2020],0)as '2020',isnull([2019],0)as '2019',isnull([2018],0)as '2018',isnull([2017],0)as '2017',isnull([2016],0)as '2016' from cte |
Partager