SQL Server Pivot Dynamic Sql with two columns

Question

Update 2022-03-04

Here is my fiddle, showing the tables and sample values

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

These are the expected results:

id	Important 1	Salary 1	Important 2	Salary 2	Important 3	Salary 3	...
1	code1	120	NULL	NULL	NULL	NULL	...
2	NULL	NULL	code2	1220	NULL	NULL	...
3	NULL	NULL	NULL	NULL	code3	120	...

I am able to get the right data for "Important 1,2,3..." using the query below. But am unable to insert data for "Salary1,2,3,..." since pivot only allows one series of columns.

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;

Results:

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   

---

Original Post:

I want to be able to generate this example with the correct data

 ------------------------------------------------------------------------------------------------
| id  | whatever | important 1 | custom 1 | important 2 | custom 2 | important 3 | custom 3 |...|
|-----------------------------------------------------------------------------------------------
|x1    |    a    |     NULL    |   NULL   |     code1   |   120      |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     code2   |   450    |     NULL    |   NULL     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------
|x2    |     b   |     NULL   |   NULL    |     code3    |   250     |    NULL   |     NULL |   |
------------------------------------------------------------------------------------------------

I am able to get the right data for "important1,2,3..." but unable to insert data for custom" since pivot only allows one series of columns, I can't create two pivot because they would look like this

---------------------------------------------------------------------------------------------
| id  | whatever | important 1 | important2| important3 | custom 1 | custom 2    |custom 3  |  
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------
|x    |          |              |         |             |          |             |          |   
---------------------------------------------------------------------------------------------

the "custom" column is a tempfield which must have decimal values in it and get repeated each time "important" column shows.

The "important" column header is from a table which hold names which i turned from rows into columns using pivot and assigned the desired values from a different column. but custom column doesn't exist in any table whatsoever but the values that I would like to put inside it do exist, the problem is, I am using dynamic sql to generate the "custom" column name with row_number to avoid duplicate column error.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);


SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.important) +','+     QUOTENAME( 'custom' + CAST(ROW_NUMBER() OVER (ORDER BY  c.id) as VARCHAR))  as tempfield 
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') 
  
        print @cols


    ----- this @cols
---- will print [important first row to column],[custom1],[important second row to column],[custom2]..


        set @query = 'SELECT  * into #temp from  
            (
                select c.id, c.whatever, c.name, c.customColumnValue  
               FROM tableName rp
                    inner join tblsecond c on rp.sTbId = c.id
         

           ) x
            pivot 
            (
                 max(name)
                for important in (' + @cols +')
            ) p             select * from #temp

                                  
'

execute(@query)

The sample data is here

http://sqlfiddle.com/#!18/ee935/1