Crosstab value-count query in SQL Server 2008 R2

Question

I have searched and cannot find (or understand!) How to do what I need to do. I feel a little silly asking about this because there are other examples, but I just don't get it.

Here is the data that I have in the 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 

What I need:

 [Approach Status] [GI Med Onc] [Breast Med Onc] Approached 2 1 Not Approached 0 1 Pending 1 0 

I played with the code change I found here, but ... any help is appreciated!

Answer 1

There are several different ways to convert rows to columns. One way you can do this is to use 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; 

See SQL Fiddle with Demo

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; 

See SQL Fiddle with Demo .

If you have unknown Clinic values, you will need to use 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) 

See SQL Fiddle with Demo . All queries will give the result:

 | APPROACHSTATUS | GI MED ONC | BREAST MED ONC | ------------------------------------------------ | Approached | 2 | 1 | | Not Approached | 0 | 1 | | Pending | 1 | 0 |