SQL Server pivot 行转列

将上表输出成下面的形式

这就需要用到四个聚合函数 group by，count，sum，pivot

select time1 as '日期',result as '结果',count(result) as '次数' from Test1 group by result,time1

先执行上面的SQL，输出以下格式

以上表作为新表再查询

select * from (
	select time1 as '日期',result as '结果',count(result) as '次数' from Test1 group by result,time1)  as a
		pivot(sum(次数) for 结果 in ([胜],[负])) as p

执行就得到需要的结果了

最后附上我的创表及插入数据的SQL

Create table Test1(
	id int primary key identity,
	time1 date,
	result varchar(5)
)
insert into Test1 values('2024-04-05','胜')
insert into Test1 values('2024-04-03','负')
insert into Test1 values('2024-04-03','负')
insert into Test1 values('2024-04-03','胜')
insert into Test1 values('2024-04-05','负')
insert into Test1 values('2024-04-05','胜')