Quick solution, should be enough to get you passed this hurdle

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @SAMPLE_DATA TABLE

(

SNAME VARCHAR(15) NOT NULL

,A1 CHAR(1) NOT NULL

,B1 CHAR(1) NOT NULL

,A2 CHAR(1) NOT NULL

,B2 CHAR(1) NOT NULL

);

INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('RAHUL','P','S','A','P');

INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('ISAC' ,'A','S','A','P');

INSERT INTO @SAMPLE_DATA(SNAME,A1,B1,A2,B2) VALUES ('WENDY' ,'A','S','S','S');

SELECT

X.SNAME

,X.AB

,COUNT(*) AS AB_COUNT

FROM @SAMPLE_DATA SD

CROSS APPLY (SELECT SNAME, A1 UNION ALL

SELECT SNAME, B1 UNION ALL

SELECT SNAME, A2 UNION ALL

SELECT SNAME, B2) AS X(SNAME,AB)

GROUP BY X.SNAME

,X.AB

ORDER BY X.SNAME

,X.AB

;

Results

SNAME AB AB_COUNT

--------------- ---- --------

ISAC A 2

ISAC P 1

ISAC S 1

RAHUL A 1

RAHUL P 2

RAHUL S 1

WENDY A 1

WENDY S 3