SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE code (
  id int primary key 
  , labVal varchar(50)
  , Label varchar(50)
)
INSERT INTO code 
VALUES
(1,'Code1','Important 1')
,(2,'Code2','Important 2')
,(3,'Code3','Important 3')

CREATE TABLE client (
  id int primary key
  , Salary decimal
  , fkCode int foreign key references code (id)
)
INSERT INTO client 
VALUES (1,120,3)
,(2,1220,2)
,(3,120, 1)
;

6 rows affected

SELECT * FROM code;
SELECT * FROM client;
SELECT cd.Id, c.id AS ClientId, cd.Label, cd.labVal, c.Salary, c.fkCode 
FROM   code  cd INNER JOIN client c ON c.fkcode = cd.id
ORDER BY cd.Id;


id
labVal
Label


1
Code1
Important 1

2
Code2
Important 2

3
Code3
Important 3




id
Salary
fkCode


1
120
3

2
1220
2

3
120
1




Id
ClientId
Label
labVal
Salary
fkCode


1
3
Important 1
Code1
120
1

2
2
Important 2
Code2
1220
2

3
1
Important 3
Code3
120
3


-- Expected Results [Manual]
SELECT * FROM  
(
    SELECT 
	cd.id,
        cd.label, 
        cd.labVal,
        c.salary
     FROM 
        code  cd
  inner join client c on c.fkcode = cd.id 
     
) t 
PIVOT(
    max(labVal) 
    FOR label IN (
        [Important 1]
        ,[salary1]
        ,[Important 2]
        ,[salary2]
        ,[Important 3]
        ,[salary3])
) AS pivot_table;




id
salary
Important 1
salary1
Important 2
salary2
Important 3
salary3


1
120
Code1
null
null
null
null
null

3
120
null
null
null
null
Code3
null

2
1220
null
null
Code2
null
null
null


-- Expected Results [Dynamic]
DECLARE @LabelCols AS VARCHAR(MAX),
        @SalaryCols AS VARCHAR(MAX),
        @SelectCols AS VARCHAR(MAX),
        @Query  AS VARCHAR(MAX);


SET @LabelCols = STUFF((    
        SELECT  ',' + QUOTENAME(cd.label) 
          FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
          ORDER BY cd.id
          FOR XML PATH('')
       ),1,1,'') 
                         
SET @SalaryCols = STUFF(( 
        SELECT  ',' + QUOTENAME('Salary'+ 
                      CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20)) 
                ) 
                             FROM code cd INNER JOIN client c ON c.fkcode = cd.id 
                             FOR XML PATH('')
                         ),1,1,'') 

SET @SelectCols = STUFF(( SELECT  ',' + QUOTENAME(cd.label) 
                                  + ',' +  QUOTENAME('Salary'+ 
                                           CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20))) 
                            FROM code  cd INNER JOIN client c ON c.fkcode = cd.id 
                            FOR XML PATH('')
                         ),1,1,'') 
                         
PRINT @LabelCols
PRINT @SalaryCols
PRINT @SelectCols      
                                 
SET @Query = '
       SELECT  CodeId 
              , '+ @SelectCols +'
       FROM
       (
              SELECT cd.id AS CodeId
                     , cd.label
                     , cd.labVal
                     , c.salary
                     , ''Salary''+ CAST(ROW_NUMBER() OVER (ORDER BY  cd.id) AS VARCHAR(20)) AS salaryLabel
              FROM code cd 
                      INNER JOIN client c ON c.fkcode = cd.id 
       ) x
       PIVOT
       (
             MAX(labVal)
             FOR label IN (' + @LabelCols +')
       ) p1
       PIVOT
       (
             MAX(salary)
             FOR salaryLabel IN (' + @SalaryCols +')
       ) p2
'
                    
EXECUTE (@Query)  




CodeId
Important 1
Salary1
Important 2
Salary2
Important 3
Salary3


1
Code1
120
null
null
null
null

2
null
null
Code2
1220
null
null

3
null
null
null
null
Code3
120


[Important 1],[Important 2],[Important 3]
[Salary1],[Salary2],[Salary3]
[Important 1],[Salary1],[Important 2],[Salary2],[Important 3],[Salary3]
id	salary	Important 1	salary1	Important 2	salary2	Important 3	salary3
1	120	Code1	null	null	null	null	null
3	120	null	null	null	null	Code3	null
2	1220	null	null	Code2	null	null	null