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