declare @t table(id int, name varchar) insert @t select 1 ,'a' union all select 2 , 'a' union all select 3 ,'c' union all select 1 , 'a' union all select 2, 'b' select a.id,b.name,count(c.id) from (select distinct id from @t) a cross join (select distinct name from @t) b left join @t c on a.id=c.id and c.name=b.name group by a.id,b.name order by a.id /* 1 a 2 1 b 0 1 c 0 2 a 1 2 b 1 2 c 0 3 a 0 3 b 0 3 c 1 */
insert @t select 1 ,'a'
union all select 2 , 'a'
union all select 3 ,'c'
union all select 1 , 'a'
union all select 2, 'b'
select a.id,b.name,count(c.id) from
(select distinct id from @t) a
cross join
(select distinct name from @t) b
left join @t c
on a.id=c.id and c.name=b.name
group by a.id,b.name
order by a.id
/*
1 a 2
1 b 0
1 c 0
2 a 1
2 b 1
2 c 0
3 a 0
3 b 0
3 c 1
*/