SQL Sum with Groupby

I'm looking to take a dataset like below and generate some statistics off the data. However, i'm having trouble figuring out how to get the data or if its even possible with a single query. I have different types of ports, in the below example its only user/printer/unknown, but there can be more than just those three. I also have status and again there can be more than just the statuses that are listed. I've tried using groupby, but it just doesn't seem to be the right tool since I'm wanting to group by one type, but I also need a count on each of the statuses?!? Any suggestions on how to achieve this would be greatly appreciated.

| Statu开发者_如何转开发s        | Type 

| connected   | User
| disabled    | User
| connected   | Printer
| disabled    | Printer
| connected   | User
| disabled    | Unknown
| disabled    | Unknown


Want Resuls like this:

| Type      | Connected   | Disabled

| User      | 2           | 1
| Printer   | 1           | 1
| Unknown   | 0           | 2

Just use CASE and SUM.

SELECT Type,
       SUM(CASE WHEN Status = 'connected' then 1 else 0 END) as Connected,
       SUM(CASE WHEN Status = 'disabled' then 1 else 0 END) as disabled
From Table
GROUP BY Type

Hmmm...

Something like:

SELECT type, COUNT(CASE WHEN status = 'connected' then 1 else null END) as Connected, 
    COUNT(CASE WHEN status='disabled' then 1 else null END) as Disabled
FROM myTable  
GROUP BY type

As @JNK mentioned, you can use PIVOT, but to do it dynamically, I believe you would have to construct the statement based on the available Status values.

The example below uses PIVOT with hard-coded status values, and then constructs the statement using the values from the sample data. You could also get the Status values from a table of valid statuses, etc.

create table #temp
(
    [Status] nvarchar(20),
    [Type] nvarchar(20)
)

insert into #temp values
    ('Connected', 'User'),
    ('Disabled', 'User'),
    ('Connected', 'Printer'),
    ('Disabled', 'Printer'),
    ('Connected', 'User'),
    ('Disabled', 'Unknown'),
    ('Disabled', 'Unknown')

-- pivot
select [Type], [Connected], [Disabled]
from 
    (select [Status], [Type] from #temp) t
    pivot
    (count([Status]) for [Status] in ([Connected], [Disabled])) as p    
order by [Connected] desc

-- dynamic pivot 
declare @statusList nvarchar(max),
        @pivot nvarchar(max)

-- get the list of Status values
select @statusList = coalesce(@statusList + ',', '') + '[' + [Status] + ']'
from (select distinct [Status] from #temp) t
order by [Status]

-- build the pivot statement
set @pivot = 
    'select [Type],' + @statusList + 
    ' from (select [Status], [Type] from #temp) t' +
    ' pivot (count([Status]) for [Status] in (' + @statusList + ')) p'

-- and execute it
exec (@pivot)

drop table #temp