SlNo TeamName WorkInfo ---- ------- ------- 1 Team1 0 2 Team1 0 3 Team1 1 4 Team2 1 5 Team2 1 6 Team3 0 7 Team3 1
SELECT DISTINCT TeamName, COUNT(TeamName) AS Count FROM TableA GROUP BY TeamName
TeamName Count -------- ----- Team1 3 Team2 2 Team3 2
TeamName Count1 Count0 -------- ----- ------ Team1 1 2 Team2 2 0 Team3 1 1
select TeamName, count(distinct case when WorkInfo = 1 then SlNo end) as Count1 , count(distinct case when WorkInfo = 0 then SlNo end) as Count0 from t1 group by TeamName
SELECT TeamName, [1] AS count1, [0] AS count0 FROM (SELECT TeamName, WorkInfo FROM tableA) AS sourcetable PIVOT ( COUNT(WorkInfo) FOR WorkInfo IN ([1], [0]) )AS somename
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)