Agrégation de données dans une seule instruction select sans jointure ni pivot

J'ai une table Input_Table définie comme ci-dessous

 create table Input_Table(id int, [year] varchar(100),initial int,final int) insert into Input_table values(1,'2017',1,11) insert into Input_table values(2,'2017',8,11) insert into Input_table values(3,'2017',3,33) insert into Input_table values(4,'2016',6,44) insert into Input_table values(5,'2016',6,44) insert into Input_table values(6,'2017',6,44) insert into Input_table values(7,'2016',4,8) insert into Input_table values(8,'2017',2,8)

Je dois get la sortie basée sur l'exigence montrée dans l'image sans utiliser le pivot ou JOINs entrez la description de l'image ici

Vous pouvez utiliser l'agrégation conditionnelle:

 SELECT 'Change from ' + cast(t.initial as varchar(20)) + ' to ' + cast(t.final as varchar(20)) as [data], COUNT(case when t.year = 2017 THEN 1 END) as [2017], COUNT(case when t.year = 2016 THEN 1 END) as [2016] FROM input_table t GROUP BY t.initial,t.final

Vous pouvez utiliser GROUP BY et SUM conditionnel en fonction de la colonne de l' year :

  SELECT 'Change from ' + CAST(initial AS varchar) + ' to ' + CAST(final AS varchar) AS [Data], SUM(CASE WHEN [year] = '2017' THEN 1 ELSE 0 END) AS [2017], SUM(CASE WHEN [year] = '2016' THEN 1 ELSE 0 END) AS [2016] FROM Input_Table GROUP BY initial, final ORDER BY [Data];

Vous pouvez utiliser l'expression group by pour get les résultats comme ci-dessous

  select 'change from ' + cast(initial as varchar(100))+ ' to ' + cast(final as varchar(100)) as data, sum(case when year=2017 then 1 else 0 end) [2017], sum(case when year=2016 then 1 else 0 end) [2016] from Input_Table group by 'change from ' + cast(initial as varchar(100))+ ' to ' + cast(final as varchar(100)) order by 1

Démonstration en direct