I tried with below SQL it worked :
select a.name,max(b.grp1) grp1,max(b.grp2) grp2,max(b.grp3) grp3,max(b.grp4) grp4 from test a
inner join
(
select
min(name) as name,
sum(case group_name when 'grp1' then 1 else 0 end) as grp1,
sum(case group_name when 'grp2' then 1 else 0 end) as grp2,
sum(case group_name when 'grp3' then 1 else 0 end) as grp3,
sum(case group_name when 'grp4' then 1 else 0 end) as grp4
from test
group by name
) b
on a.name=b.name
where a.group_name='grp1'
group by a.name
abc1100
ddd2100
select a.name,max(b.grp1) grp1,max(b.grp2) grp2,max(b.grp3) grp3,max(b.grp4) grp4 from test a
inner join
(
select
min(name) as name,
sum(case group_name when 'grp1' then 1 else 0 end) as grp1,
sum(case group_name when 'grp2' then 1 else 0 end) as grp2,
sum(case group_name when 'grp3' then 1 else 0 end) as grp3,
sum(case group_name when 'grp4' then 1 else 0 end) as grp4
from test
group by name
) b
on a.name=b.name
where a.group_name='grp2'
group by a.name
aaa0121
abc1100
ccc0101
ddd2100
eee0100
But, I don't want the column when grp1 when I am saying for group_name='grp1', and don't want grp2 column when I am saying where group_name='grp2' like that.