SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table YourTable (
 id int primary key identity(1,1), 
 [Name] varchar(100) not null, 
 Qualification varchar(30) not null, 
 Grade char(1) not null
);
insert into YourTable ([Name], Qualification, Grade) values
('Antony','HSE','A'),
('Antony','Degree','C'),
('Antony','Masters','B'),
('Antony','Gamer','X'),
('Bob','HSE','B'),
('Bob','Degree','B'),
('Bob','Masters','A'),
('Bob','Husband','D'),
('Marc','HSE','D'),
('Marc','Degree','C'),
('Marc','Masters','B'),
('Marc','Beer','A');
12 rows affected

SELECT
Name,
MAX(CASE WHEN Qualification = 'HSE' THEN Qualification ELSE '' END) AS Q1,
MAX(CASE WHEN Qualification = 'HSE' THEN Grade ELSE '' END) AS G1,
MAX(CASE WHEN Qualification = 'Degree' THEN Qualification ELSE '' END) AS Q2,
MAX(CASE WHEN Qualification = 'Degree' THEN Grade ELSE '' END) AS G2,
MAX(CASE WHEN Qualification = 'Masters' THEN Qualification ELSE '' END) AS Q3,
MAX(CASE WHEN Qualification = 'Masters' THEN Grade ELSE '' END) AS G3
FROM YourTable
GROUP BY Name
ORDER BY Name


Name
Q1
G1
Q2
G2
Q3
G3


Antony
HSE
A
Degree
C
Masters
B

Bob
HSE
B
Degree
B
Masters
A

Marc
HSE
D
Degree
C
Masters
B


SELECT
Name,
MAX(CASE WHEN RN = 1 THEN Qualification END) AS Q1,
MAX(CASE WHEN RN = 1 THEN Grade END) AS G1,
MAX(CASE WHEN RN = 2 THEN Qualification END) AS Q2,
MAX(CASE WHEN RN = 2 THEN Grade END) AS G2,
MAX(CASE WHEN RN = 3 THEN Qualification END) AS Q3,
MAX(CASE WHEN RN = 3 THEN Grade END) AS G3
FROM 
( 
   SELECT Name, Qualification, Grade,
   ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Qualification) AS RN
   FROM YourTable
) q
GROUP BY Name
ORDER BY Name


Name
Q1
G1
Q2
G2
Q3
G3


Antony
Degree
C
Gamer
X
HSE
A

Bob
Degree
B
HSE
B
Husband
D

Marc
Beer
A
Degree
C
HSE
D


Warning: Null value is eliminated by an aggregate or other SET operation.


declare @MaxTotalQualifications int = (select top 1 count(*) from YourTable group by [Name] order by count(*) desc);

declare @cols varchar(max);

WITH DIGITS(n) AS (
 SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
, NUMBERS(n) AS
(
 SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
 FROM DIGITS AS ones 
 CROSS JOIN DIGITS as tens 
 CROSS JOIN DIGITS as hundreds 
 CROSS JOIN DIGITS as thousands
)
select @cols = concat(@cols+CHAR(13)+CHAR(10)+', ', 'MAX(CASE WHEN RN = ', n ,' THEN Qualification END) AS [Q', n ,'], MAX(CASE WHEN RN = ', n ,' THEN Grade END) AS [G', n,']')
from NUMBERS
WHERE n BETWEEN 1 AND @MaxTotalQualifications;

-- select @MaxTotalQualifications as MaxTotalQualifications, @cols as cols;

declare @DynSql nvarchar(max);

set @DynSql = N'SELECT Name, '+ @cols + N'
FROM 
( 
   SELECT Name, Qualification, Grade,
   ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Qualification) AS RN
   FROM YourTable
) q
GROUP BY Name
ORDER BY Name';

-- select @DynSql as DynSql;

exec(@DynSql);


Name
Q1
G1
Q2
G2
Q3
G3
Q4
G4


Antony
Degree
C
Gamer
X
HSE
A
Masters
B

Bob
Degree
B
HSE
B
Husband
D
Masters
A

Marc
Beer
A
Degree
C
HSE
D
Masters
B


Warning: Null value is eliminated by an aggregate or other SET operation.
Name	Q1	G1	Q2	G2	Q3	G3
Antony	HSE	A	Degree	C	Masters	B
Bob	HSE	B	Degree	B	Masters	A
Marc	HSE	D	Degree	C	Masters	B
Name	Q1	G1	Q2	G2	Q3	G3
Antony	Degree	C	Gamer	X	HSE	A
Bob	Degree	B	HSE	B	Husband	D
Marc	Beer	A	Degree	C	HSE	D