Updating Values In One Table From Another Table Using Dynamic Sql In Mssql

I have a temp table A having 2 columns col1: ID col2: Value generated using XML. I need to update the columns in table B corresponding to column1:ID of table A with values present

Solution 1:

Try following query:

UPDATE TableB
SET [1] = ISNULL(z.[1],TableB.[1]),
    [2] = ISNULL(z.[2],TableB.[2]),
    [3] = ISNULL(z.[3],TableB.[3]),
    [4] = ISNULL(z.[4],TableB.[4]),
    [5] = ISNULL(z.[5],TableB.[5]),
    [6] = ISNULL(z.[6],TableB.[6]),
    [7] = ISNULL(z.[7],TableB.[7])
FROM (
    SELECT [1],[2],[3],[4],[5],[6],[7] 
    FROM (SELECT Id, ValueFROM TableA)AS p
    PIVOT (MAX(Value) FOR Id IN([1],[2],[3],[4],[5],[6],[7]))AS pvt
    )z

EDIT

In order to have dynamic pivot use following query:

DECLARE@columns1 NVARCHAR(1000) ='',
        @columns2 NVARCHAR(1000) ='',
        @sql NVARCHAR(MAX)

SELECT@Columns1= STUFF((SELECT',['+Value+'] = ISNULL(z.['+Value+'],TableB.['+Value+'])'FROM (SELECTDISTINCTValueFROM TableA)z
                        FOR XML PATH('')),1,1,''),
        @Columns2= STUFF((SELECT',['+Value+']'FROM (SELECTDISTINCTValueFROM TableA)z
                        FOR XML PATH('')),1,1,'')


SET@sql='Update TableB
            Set '+@columns1+' 
            From ( 
                  Select '+@columns2+'
                  From (Select Id, Value From TableA) AS p
                  Pivot (MAX(Value) For Id IN ('+@columns2+')) AS Pvt
            )z'EXECUTE(@sql)

Solution 2:

INSERT INTO tb  
SELECT [1],[2],[3],[4],[5],[6],[7]FROM
(SELECT 
id,value
  from ta)as p
PIVOT
(AVG(value) FOR id IN([1],[2],[3],[4],[5],[6],[7])
 )as bah

Fiddle