sql-server - 在单个 select 语句中聚合数据，无需连接和透视

Question

我有一个表Input_Table定义如下

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)

我需要根据图中所示的要求获得输出，而不使用枢轴或连接

Answer 1

您可以使用条件聚合:

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