You'll have much better luck getting answers in the future if you make your tables and sample data in a readily consumable manner instead of just posting plain text like you did. Please see the first article under "Helpful Links" in my signature line below for a method to do that properly.

Since you're a newbie, I'll do it for you this time. The following creates the test data you gave in a test table and then offers a high performance CROSSTAB as a solution to not only populate Table 1, but create it, as well. You shouldn't need an update for this.

--===== Build and populate the test table on the fly.

-- This is NOT a part of the solution. We' just

-- building some test data here.

SELECT PID,ID,[Value]

INTO #Table2

FROM (--==== Test data

SELECT 1,1,10 UNION ALL

SELECT 1,2,20 UNION ALL

SELECT 1,3,30 UNION ALL

SELECT 1,4,12 UNION ALL

SELECT 1,5,32 UNION ALL

SELECT 1,6,12 UNION ALL

SELECT 2,1,11 UNION ALL

SELECT 2,2,21 UNION ALL

SELECT 2,3,30 UNION ALL

SELECT 2,4,15 UNION ALL

SELECT 2,5,32 UNION ALL

SELECT 2,6,12 UNION ALL

SELECT 3,1,18 UNION ALL

SELECT 3,2,20 UNION ALL

SELECT 3,3,33 UNION ALL

SELECT 3,4,12 UNION ALL

SELECT 3,5,42 UNION ALL

SELECT 3,6,52

)d(PID,ID,[Value])

;

--===== Pivot the data using the ancient high performance

-- method of a "CROSSTAB" to build Table 1.

SELECT PID

,ID1 = SUM(CASE WHEN ID=1 THEN [Value] ELSE 0 END)

,ID2 = SUM(CASE WHEN ID=2 THEN [Value] ELSE 0 END)

,ID3 = SUM(CASE WHEN ID=3 THEN [Value] ELSE 0 END)

,ID4 = SUM(CASE WHEN ID=4 THEN [Value] ELSE 0 END)

,ID5 = SUM(CASE WHEN ID=5 THEN [Value] ELSE 0 END)

,ID6 = SUM(CASE WHEN ID=6 THEN [Value] ELSE 0 END)

INTO #Table1

FROM #Table2

GROUP BY PID

;

--===== Display the content of the new Table1

SELECT *

FROM #Table1

;

Results:

PID ID1 ID2 ID3 ID4 ID5 ID6

----------- ----------- ----------- ----------- ----------- ----------- -----------

1 10 20 30 12 32 12

2 11 21 30 15 32 12

3 18 20 33 12 42 52

(3 row(s) affected)

If it absolutely has to be done as an update, then just use the CROSSTAB as a CTE and join to it for the update.