具有SQL Server 2008 R2中的值计数的交叉表查询 [英] Crosstab query with count of values in SQL Server 2008 R2
问题描述
我已经搜索过,但没有发现(或理解!)如何做我需要做的事情.既然还有其他示例,我对此感到有点愚蠢,但我只是不明白....
I've searched and am not finding (or understanding!) how to do what I need to do. I feel a bit silly asking this since there are other examples, but I'm just not getting it....
这是我在视图中拥有的数据:
Here's the data I have in a view:
[Approach Status] [Clinic]
Approached GI Med Onc
Pending GI Med Onc
Approached GI Med Onc
Not Approached Breast Med Onc
Approached Breast Med Onc
我需要的是:
[Approach Status] [GI Med Onc] [Breast Med Onc]
Approached 2 1
Not Approached 0 1
Pending 1 0
我一直在修改在这里找到的代码,但是..任何帮助,我们都感激不尽!
I've played with modifying code I found on here but.... any help is appreciated!
推荐答案
有几种不同的方法可以将行转换为列.一种实现方法是使用带有CASE表达式的聚合函数:
There are a few different ways that you can convert the rows to columns. One way that you can do this is by using an aggregate function with a CASE expression:
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小提琴
或者由于使用的是SQL Server 2005+,因此可以使用PIVOT函数:
Or since you are using SQL Server 2005+, you can use the PIVOT function:
select ApproachStatus, [GI Med Onc],[Breast Med Onc]
from yt
pivot
(
count(Clinic)
for Clinic in ([GI Med Onc],[Breast Med Onc])
) piv;
请参见带演示的SQL提琴.
如果您有未知的Clinic
值,那么您将需要使用动态SQL来获得结果:
If you have an unknown Clinic
values, then you will need to look at using dynamic SQL to get the result:
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提琴.所有查询都会给出结果:
See SQL Fiddle with Demo. All queries will give a result:
| APPROACHSTATUS | GI MED ONC | BREAST MED ONC |
------------------------------------------------
| Approached | 2 | 1 |
| Not Approached | 0 | 1 |
| Pending | 1 | 0 |
这篇关于具有SQL Server 2008 R2中的值计数的交叉表查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!