using count keyword with pivot table

create table t1 (

 id int,

 attribute varchar(50),

 value varchar(100)

)

--Code for Insert Value in Table with columns (id,attribute,value)             

insert into t1(id, attribute, value)

values  

 (1, 'stu_name', 'Rahul'),

 (1, 'address', 'Mahipalpur'),

 (1, 'Qualification', 'BTech'),

 (2, 'Stu_name', 'Sakshi'),

 (2, 'address', 'Greater Noida'),

 (2, 'Qualification', 'BTech'),

 (3, 'Stu_name', 'Kush Tiwari'),

 (3, 'address', 'Noida'),

 (3, 'Qualification', 'MCA'),

 (4, 'Stu_name', 'Kush Tiwari'),

 (4, 'address', 'Noida'),

 (4, 'Qualification', 'MCA'),

 (5, 'Stu_name', 'Sakshi'),

 (5, 'address', 'Greater Noida'),

 (5, 'Qualification', 'BTech'),

 (6, 'stu_name', 'Rahul'),

 (6, 'address', 'Mahipalpur'),

 (6, 'Qualification', 'BTech')

--Code to show pivot list             

select p.id,

p.[stu_name],

p.[address],

p.[qualification]

from t1  pivot(min(value) for attribute in([stu_name],[address],[qualification])) p

1        Rahul                         Mahipalpur              BTech

2        Sakshi                       Greater Noida          BTech

3        Kush Tiwari                Noida                     MCA

4        Kush Tiwari                Noida                     MCA

5        Sakshi                      Greater Noida           BTech

6        Rahul                        Mahipalpur              BTech

--Code to show Repeated Number and name             

select count(t.[stu_name] ) as 'total Repeated name',t.[stu_name] as 'student name' from

(select p.id,

p.[stu_name],

p.[address],

p.[qualification]

from t1  pivot(min(value) for attribute in([stu_name],[address],[qualification])) p ) as t group by t.[stu_name]

total Repeated Name             Name

2                                      Kush Tiwari

2                                      Rahul

2                                      Sakshi