Crosstab query with count of values in SQL Server 2008 R2

Question

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  

What I need is:

[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!

Solution

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;

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 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)

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 |

Comments

[Clint Finch]

Thanks. The first example worked, but the second (preferred) is returning this: GI Med Onc Breast Med Onc Approached 1 0 Pending 1 0 Approached 1 0 Not Approached 0 1 Approached 0 1

[Taryn]

@ClintFinch Can you edit the demo (sqlfiddle.com/#!3/ef8f0/3) with some of your sample data and the query you are using?

[Clint Finch]

Odd.... I'm getting the right values when I use the sqlfiddle.... The only diff is that my data source is a view with many columns (not just the two I'm referencing here) and not a table, should that matter? sqlfiddle.com/#!3/a4645/1/0

[Taryn]

@ClintFinch If you are returning more columns then what you need for the pivot then you can alter the result because the columns are used in the grouping the pivot applies. If I add a column with distinct value to multiple rows and then I attempt to pivot, the result will be skewed because of the additional columns. You should only include the columns you need to display in a pivot.

[Clint Finch]

ok, so why would it work in sqlfiddle an not in Management Studio? The example does include all the ones I need - I edited out the others for sake of brevity in my post. these are the distinct values in the Clinic column (only Prostate has not been recorded yet - will be) Unknown Breast Med Onc Otolaryngology Ourisman No Clinic Data GI Med Onc

[Taryn]

@ClintFinch Are you only selecting clinic and ApproachStatus or do you have other columns in your select list?

[Clint Finch]

I have many other columns in my view, vw_NTSR_Base_AllRecords_Labels.

[Taryn]

Without seeing your actual data it is difficult, but if you only want to display the clinic and the ApproachStatus, then those should be the only two columns that you are selecting from your view. If you select other columns, then those will be used in the grouping aspect of the pivot and if the values are different for each row, then the result can be staggered.

[Clint Finch]

That fixed it! Thanks very much, I'll create separate views for each of these. You've been a great help!

[Taryn]

@ClintFinch Glad you figured it out! :)

[YvesR]

+1 helped me to solve similar issue. @ClintFinch you should mark it as answer.