SQL Server 2012 Query Several, but not all, columns

Question

I am creating a query for an application in SQL Server 2012. This query should capture all kinds of statuses from a single table from SQL Server.

The tables are structured as follows:

ProjectType | Team | ProjectStatus ---------------------------------- Proj1 B Rec Proj1 B Rec Proj1 B Hold Proj2 B Rec Proj3 A Hold Proj4 C Some 

My desired result:

 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 have:

All statuses

 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(ProjectStatus) as cnt from Sites Where ProjectStatus = 'Rec' group By ProjectType) C on S.ProjectType = c.ProjectType 

That's where I thought to add

 Inner Join ( Select ProjectType, Count(ProjectStatus) as cnt from Sites Where ProjectStatus='Rec' group By ProjectType) C on S.ProjectType = c.ProjectType 

In the status that I have to count them individually ...

EDIT: but the result is incorrect, it calculated all the same ProjectType for all different ones (for example, I had 10 diff), it displayed all the same 10 times over

Is there a better way to do this - can someone help me fill out?

Answer 1

Depending on your question, it seems that you have several statuses (all known) and you want to show status calculations - placing statuses on top. There are ways to do this with PIVOT, but right now I'm not on a computer with my dev resources, so here's 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