Name Date Des
jason 2010-11-10 yes
barry 2010-11-12 yes
barry 2010-11-10 no
barry 2010-11-12 yes
jason 2010-11-10 yes
jason 2010-11-12 no
显示结果:
yes no
jason 2010-11-10 2 0
jason 2010-11-12 0 1
barry 2010-11-10 0 1
barry 2010-11-12 2 0
用Group by 如何做到这种显示效果
------解决方案--------------------
- SQL code
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([Name] nvarchar(5),[Date] Datetime,[Des] nvarchar(3))Insert #Tselect N'jason','2010-11-10',N'yes' union allselect N'barry','2010-11-12',N'yes' union allselect N'barry','2010-11-10',N'no' union allselect N'barry','2010-11-12',N'yes' union allselect N'jason','2010-11-10',N'yes' union allselect N'jason','2010-11-12',N'no'GoSelect [Name],sum(CASE WHEN [Des]='yes' THEN 1 ELSE 0 END) AS yes,sum(CASE WHEN [Des]='no' THEN 1 ELSE 0 END) AS [no]from #T GROUP BY [Name],[Date]ORDER BY 1,2/*Name yes nobarry 0 1barry 2 0jason 0 1jason 2 0*/