SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table #Temp (
    SLno int,
    Name varchar(20),
    Position varchar(20),
    [Group] Varchar(20)
)

insert into #Temp
values
    ( 1, 'John',   'Painter', 'India'),
    ( 2, 'MArk',   'Washer',  'India'),
    ( 3, 'Peter',  'Officer', 'Nepal'),
    ( 4, 'Angel',  'Officer', 'India'),
    ( 5, 'Bibin',  'Washer',  'Nepal'),
    ( 6, 'Jerry',  'Painter', 'Nepal'),
    ( 7, 'Lilly',  'Painter', 'India'),
    ( 8, 'George', 'Washer',  'India'),
    ( 9, 'Abrahm', 'Officer', 'Nepal'),
    (10, 'Bosan',  'Officer', 'India'),
    (11, 'Jemi',   'Painter', 'Nepal'),
    (12, 'Jerry',  'Washer',  'Nepal'),
    (21, 'Larry',  'Officer', 'Nepal'),
    (22, 'Curly',  'Painter', 'Nepal'),
    (23, 'Moe',    'Washer',  'Nepal')
15 rows affected

WITH CTE_NumberedPositions AS (
    SELECT
        ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No,
        CONCAT(Position, '_', [Group]) AS Combined,
        Name
    FROM #Temp
)
SELECT
    No,
    [Painter_India], [Painter_Nepal], [Washer_India], [Washer_Nepal], [Officer_India], [Officer_Nepal]
FROM CTE_NumberedPositions
PIVOT (
    MAX(Name)
    FOR Combined IN ([Painter_India], [Painter_Nepal], [Washer_India], [Washer_Nepal], [Officer_India], [Officer_Nepal])
) PVT
ORDER BY PVT.No


No
Painter_India
Painter_Nepal
Washer_India
Washer_Nepal
Officer_India
Officer_Nepal


1
John
Jerry
MArk
Bibin
Angel
Peter

2
Lilly
Jemi
George
Jerry
Bosan
Abrahm

3
null
Curly
null
Moe
null
Larry


WITH CTE_NumberedPositions AS (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No
    FROM #Temp
)
SELECT
    No,
    MAX(CASE WHEN Position = 'Painter' AND [Group] = 'India' THEN Name END) AS [Painter_India],
    MAX(CASE WHEN Position = 'Painter' AND [Group] = 'Nepal' THEN Name END) AS [Painter_Nepal],
    MAX(CASE WHEN Position = 'Washer' AND [Group] = 'India' THEN Name END) AS [Washer_India],
    MAX(CASE WHEN Position = 'Washer' AND [Group] = 'Nepal' THEN Name END) AS [Washer_Nepal],
    MAX(CASE WHEN Position = 'Officer' AND [Group] = 'India' THEN Name END) AS [Officer_India],
    MAX(CASE WHEN Position = 'Officer' AND [Group] = 'Nepal' THEN Name END) AS [Officer_Nepal]
FROM CTE_NumberedPositions
GROUP BY No
ORDER BY No


No
Painter_India
Painter_Nepal
Washer_India
Washer_Nepal
Officer_India
Officer_Nepal


1
John
Jerry
MArk
Bibin
Angel
Peter

2
Lilly
Jemi
George
Jerry
Bosan
Abrahm

3
null
Curly
null
Moe
null
Larry


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


DECLARE @Columns NVARCHAR(MAX) = (
    SELECT STRING_AGG(QUOTENAME(CONCAT(A.Position, '_', B.[Group])), ', ')
        WITHIN GROUP(ORDER BY A.Position, B.[Group])
    FROM (SELECT DISTINCT Position FROM #Temp) A
    CROSS JOIN (SELECT DISTINCT [Group] FROM #Temp) B
)

DECLARE @Sql NVARCHAR(MAX) = '
    WITH CTE_NumberedPositions AS (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No,
            CONCAT(Position, ''_'', [Group]) AS Combined,
            Name
        FROM #Temp
    )
    SELECT
        No, 
        ' + @Columns + '
    FROM CTE_NumberedPositions
    PIVOT (
        MAX(Name)
        FOR Combined IN (' + @Columns + ')
    ) PVT
    ORDER BY PVT.No
'

PRINT @Sql
EXEC (@Sql)


No
Officer_India
Officer_Nepal
Painter_India
Painter_Nepal
Washer_India
Washer_Nepal


1
Angel
Peter
John
Jerry
MArk
Bibin

2
Bosan
Abrahm
Lilly
Jemi
George
Jerry

3
null
Larry
null
Curly
null
Moe



    WITH CTE_NumberedPositions AS (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No,
            CONCAT(Position, '_', [Group]) AS Combined,
            Name
        FROM #Temp
    )
    SELECT
        No, 
        [Officer_India], [Officer_Nepal], [Painter_India], [Painter_Nepal], [Washer_India], [Washer_Nepal]
    FROM CTE_NumberedPositions
    PIVOT (
        MAX(Name)
        FOR Combined IN ([Officer_India], [Officer_Nepal], [Painter_India], [Painter_Nepal], [Washer_India], [Washer_Nepal])
    ) PVT
    ORDER BY PVT.No



DECLARE @Columns NVARCHAR(MAX) = (
    SELECT STRING_AGG(S.Item, ',' + CHAR(13) + CHAR(10))
        WITHIN GROUP(ORDER BY A.Position, B.[Group])
    FROM (SELECT DISTINCT Position FROM #Temp) A
    CROSS JOIN (SELECT DISTINCT [Group] FROM #Temp) B
    CROSS APPLY (
         SELECT item =
              '        MAX(CASE WHEN Position = '
              + QUOTENAME(A.Position, '''')
              + ' AND [Group] = '
              + QUOTENAME(B.[Group], '''')
              + ' THEN Name END) AS '
              + QUOTENAME(CONCAT(A.Position, '_', B.[Group]))
    ) S
)

DECLARE @Sql NVARCHAR(MAX) = '
    WITH CTE_NumberedPositions AS (
        SELECT *,
            ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No
        FROM #Temp
    )
    SELECT
        No,
' + @Columns + '
    FROM CTE_NumberedPositions
    GROUP BY No
    ORDER BY No
'

PRINT @Sql
EXEC (@Sql)


No
Officer_India
Officer_Nepal
Painter_India
Painter_Nepal
Washer_India
Washer_Nepal


1
Angel
Peter
John
Jerry
MArk
Bibin

2
Bosan
Abrahm
Lilly
Jemi
George
Jerry

3
null
Larry
null
Curly
null
Moe



    WITH CTE_NumberedPositions AS (
        SELECT *,
            ROW_NUMBER() OVER(PARTITION BY Position, [Group] ORDER BY SLNo) AS No
        FROM #Temp
    )
    SELECT
        No,
        MAX(CASE WHEN Position = 'Officer' AND [Group] = 'India' THEN Name END) AS [Officer_India],
        MAX(CASE WHEN Position = 'Officer' AND [Group] = 'Nepal' THEN Name END) AS [Officer_Nepal],
        MAX(CASE WHEN Position = 'Painter' AND [Group] = 'India' THEN Name END) AS [Painter_India],
        MAX(CASE WHEN Position = 'Painter' AND [Group] = 'Nepal' THEN Name END) AS [Painter_Nepal],
        MAX(CASE WHEN Position = 'Washer' AND [Group] = 'India' THEN Name END) AS [Washer_India],
        MAX(CASE WHEN Position = 'Washer' AND [Group] = 'Nepal' THEN Name END) AS [Washer_Nepal]
    FROM CTE_NumberedPositions
    GROUP BY No
    ORDER BY No

Warning: Null value is eliminated by an aggregate or other SET operation.
No	Painter_India	Painter_Nepal	Washer_India	Washer_Nepal	Officer_India	Officer_Nepal
1	John	Jerry	MArk	Bibin	Angel	Peter
2	Lilly	Jemi	George	Jerry	Bosan	Abrahm
3	null	Curly	null	Moe	null	Larry