具有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屋！