SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE _Table 
(
    [Pat] NVARCHAR(8), 
    [Codes] NVARCHAR(50), 
    [C1] NVARCHAR(6), 
    [C2] NVARCHAR(6), 
    [C3] NVARCHAR(6), 
    [C4] NVARCHAR(6), 
    [C5] NVARCHAR(6)
);

INSERT INTO _Table ([Pat], [Codes], [C1], [C2], [C3], [C4], [C5])
VALUES
    ('Pat1', 'U212,Y973,Y982', null, null, null, null, null),
    ('Pat2', 'M653', null, null, null, null, null), 
    ('Pat3', 'U212,Y973,Y983,Z924,Z926', null, null, null, null, null);
3 rows affected

;WITH tmp([Pat], [Codes],x, lvl) AS
(
    SELECT
        Pat,
        Codes = CAST(STUFF(Codes, 1, CHARINDEX(',', Codes + ','), '')AS NVARCHAR(MAX)), 
        x = CAST(LEFT(Codes, CHARINDEX(',', Codes + ',') - 1) AS NVARCHAR(MAX)),
        lvl = 1
    FROM _Table
    UNION ALL
    SELECT  Pat,
        Codes = STUFF(Codes, 1, CHARINDEX(',', Codes + ','), ''), 
        x= LEFT(Codes, CHARINDEX(',', Codes + ',') - 1),
        lvl = lvl+1
    FROM tmp
    WHERE Codes > ''
), cte2 AS (
SELECT Pat
    ,c1 = MAX(IIF(lvl=1, x, NULL))
    ,c2 = MAX(IIF(lvl=2, x, NULL))
    ,c3 = MAX(IIF(lvl=3, x, NULL))
    ,c4 = MAX(IIF(lvl=4, x, NULL))
    ,c5 = MAX(IIF(lvl=5, x, NULL))
FROM tmp 
GROUP BY Pat
)
UPDATE _Table
SET c1 = c.c1
   ,c2 = c.c2
   ,c3 = c.c3
   ,c4 = c.c4
   ,c5 = c.c5
FROM _Table t
JOIN cte2 c
  ON t.Pat = c.Pat
 OPTION (MAXRECURSION 0);
3 rows affected

SELECT *
FROM _Table;


Pat
Codes
C1
C2
C3
C4
C5


Pat1
U212,Y973,Y982
U212
Y973
Y982
null
null

Pat2
M653
M653
null
null
null
null

Pat3
U212,Y973,Y983,Z924,Z926
U212
Y973
Y983
Z924
Z926

Pat	Codes	C1	C2	C3	C4	C5
Pat1	U212,Y973,Y982	U212	Y973	Y982	null	null
Pat2	M653	M653	null	null	null	null
Pat3	U212,Y973,Y983,Z924,Z926	U212	Y973	Y983	Z924	Z926