SQL Server 2012 Query Multiple but not all Column Counts

Question

I'm building a query for an application in SQL Server 2012. This query is supposed to grab all sorts of statuses from a single table from SQL Server. Tables are structured like this: ``` ProjectType | Team | ProjectStatus ---------------------------------- Proj1 B Rec Proj1 B Rec Proj1 B Hold Proj2 B Rec Proj3 A Hold Proj4 C Some ``` My wanted output would be: ``` ProjectType | Total | Team | Rec| Hold | Some | ----------------------------------------------- Proj1 3 B 2 1 0 Proj2 1 B 1 0 0 Proj3 1 A 0 1 0 Proj4 1 C 0 0 1 ``` I think this is possible because I know all the statuses they will ever be: All statuses are ``` Rec, BA, DQ, P, Prev, PRed, PCom, 90, 90Rev, 90Red, 90Com, SS, SSRed, D, C ``` What I have tried so far: ``` select ProjectType, Team, count(ProjectStatus) from sites where (ProjectType is not null and ProjectType <> '') and Team <> '' group by ProjectType, Team select s.ProjectType, S.Team, S.ProjectType, C.cnt from Sites s Inner Join (Select ProjectType, Count(Projec…

Accepted Answer

Based on your question, it seems you have multiple statuses (all known) and want to show the counts by status -- placing the statuses across the top. There are ways to do this using PIVOT, but I'm not at a computer with my dev resources right now, so this is how I would do it (out of my head -- untested).

```
select 
  ProjectType,
  count(*) as Total,
  Team,
  sum(case when ProjectStatus = 'Rec' then 1 else 0 end) as 'Rec',
  sum(case when ProjectStatus = 'Hold' then 1 else 0 end) as 'Hold',
  sum(case when ProjectStatus = 'Some' then 1 else 0 end) as 'Some'
from
  sites
where
  ProjectStatus in ('Rec', 'Hold', 'Some')
group by
  ProjectType,
  Team
```

SQLFiddle

SQL Server 2012 Query Multiple but not all Column Counts

Solution

Problem