您可以通过使用带有CASE表达式的聚合函数来将行转换为列,有几种不同的方法可以实现这一点:
select ApproachStatus,
sum(case when Clinic = 'GI Med Onc' then 1 else 0 end) [GI Med Onc],
sum(case when Clinic = 'Breast Med Onc' then 1 else 0 end) [Breast Med Onc]
from yt
group by ApproachStatus;
请查看带演示的SQL Fiddle
如果您使用SQL Server 2005以上版本,您可以使用PIVOT函数:
select ApproachStatus, [GI Med Onc],[Breast Med Onc]
from yt
pivot
(
count(Clinic)
for Clinic in ([GI Med Onc],[Breast Med Onc])
) piv
请查看带有演示的 SQL Fiddle。
如果您有未知的Clinic值,则需要使用动态SQL来获得结果:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Clinic)
from yt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ApproachStatus,' + @cols + '
from yt
pivot
(
count(Clinic)
for Clinic in (' + @cols + ')
) p '
execute(@query)
请查看带演示的SQL Fiddle:
http://sqlfiddle.com/#!3/ef8f0/6。所有查询都将得到结果:
| APPROACHSTATUS | GI MED ONC | BREAST MED ONC |
| Approached | 2 | 1 |
| Not Approached | 0 | 1 |
| Pending | 1 | 0 |
GI Med Onc Breast Med Onc
Approached 1 0Pending 1 0
Approached 1 0
Not Approached 0 1
Approached 0 1 - Clint Finch