split columns (more than one) into multiple columns with dynamic sql

Question

I have table with data as below

I have got result to convert one column to multiple columns. But I need output to convert multiple columns

The result is expected as below

Output needed as

Name    Q1      G1      Q2      G2     Q3        G3  
Antony  HSE      A     Degree   C      NULL      NULL
Bob     HSE      B     Degree   B      Masters   A
Marc    HSE      D     Degree   C      Masters   B

Answer 1

If those Qualifications have fixed values, then you can get that result via conditional aggregation.

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

If the qualification names aren't fixed, then you could generate a row_number and use that.
Then you can add as many Qn & Gn as a Name can have qualifications.
To test that: select top 1 [Name], count(*) Total from @YourTable group by [Name] order by Total desc

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

Or doing it dynamic

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);

A test on db<>fiddle here