Divide data in single column to multiple columns

Question

I have source data like

Name Is_Trget
a Yes
s No
d Yes
f Yes
a Yes
S No

from this source data expected output is

Name Is_Yes Is_No
a 2 0
s 0 2
d 1 0
f 1 0

I need count of Yes and No for a specific Name.

Can someone help on this.

Answer 1

SELECT Name, 
 SUM(CASE Is_Trget WHEN 'Yes' THEN 1 ELSE 0 END) Is_Yes,
 SUM(CASE Is_Trget WHEN 'No' THEN 1 ELSE 0 END) Is_No
FROM source
GROUP BY Name;

fiddle

Answer 2

Here is another approach using PIVOT

SELECT Name
, Is_Yes = Yes
, Is_No = No
FROM YourTable AS A
PIVOT (COUNT(Is_Trget) FOR Is_Trget IN ([Yes], [No])) AS PivotTable