Update multiple columns with the last values ​​of another table

Here's the data:

[ TABLE_1 ]

id | prod1  | date1  | prod2  | date2  | prod3  | date3 |
---|--------|--------|--------|--------|--------|-------|
1  | null   | null   | null   | null   | null   | null  |
2  | null   | null   | null   | null   | null   | null  |
3  | null   | null   | null   | null   | null   | null  |

[ TABLE_2 ]

id   |  date       |   product |
-----|-------------|-----------|
1    |  20140101   |   X       |
1    |  20140102   |   Y       |
1    |  20140103   |   Z       | 

2    |  20141201   |   data    |
2    |  20141201   |   Y       |
2    |  20141201   |   Z       | 

3    |  20150101   |   data2   |
3    |  20150101   |   data3   |
3    |  20160101   |   X       |

Both tables have other columns not listed here. date is formatted: yyyymmdd and datatype is int. [ TABLE_2 ] doesn't have empty rows, just tried to make sample above more readable.

Here's the Goal:

I need to update [ TABLE_1 ] prod1,date1,prod2,date2,prod3,date3 with productcollected from [ TABLE_2 ] with corresponding date values.

Data must be sorted so that "latest" product becomes prod1, 2nd latest product will be prod2 and 3rd is prod3.

Latest product = biggest date (int). If dates are equal, order doesn't matter. (see id=2 and id=3).

Updated [ TABLE_1 ] should be:

id | prod1  | date1    | prod2  | date2    | prod3  | date3    |
---|--------|----------|--------|----------|--------|----------|
1  | Z      | 20140103 | Y      | 20140102 | X      | 20140101 |
2  | data   | 20141201 | Y      | 20141201 | Z      | 20141201 |
3  | X      | 20160101 | data2  | 20150101 | data3  | 20150101 |

Ultimate goal is to get the following :

[ TABLE_3 ]

id | order1             | order2               | order3     |  + Columns from [ TABLE_1 ]
---|--------------------|----------------------|------------|--------------------------
1  | 20140103:Z         | 20140102:Y           | 20140103:Z |
2  | 20141201:data:Y:Z  | NULL                 | NULL       |
3  | 20160101:X         | 20150101:data2:data3 | NULL       |

I have to admit this exceeds my knowledge and I haven't tried anything.

Should I do it with JOIN or SELECT subquery?

Should I try to make it in one SQL -clause or perhaps in 3 steps, each prod&date -pair at the time ?

What about creating [ TABLE_3 ] ? It has to have columns from [ TABLE_1 ]. Is it easiest to create it from [ TABLE_2 ] -data or Updated [ TABLE_1 ] ?

Any help would be highly appreciated. Thanks in advance.

I'll post some of my own shots on comments.

---

After looking into it (after my comment), a stored procedure would be best, that you can call to view the data as a pivot, and do away with TABLE_1. Obviously if you need to make this dynamic, you'll need to look into dynamic pivots, it's a bit of a hack with CTEs:

CREATE PROCEDURE DBO.VIEW_AS_PIVOTED_DATA

AS

;WITH CTE AS (
SELECT ID, [DATE], 'DATE' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [DATE] DESC) AS VARCHAR) AS [RN]
FROM TABLE_2)
, CTE2 AS (
SELECT ID, PRODUCT, 'PROD' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [DATE] DESC) AS VARCHAR) AS [RN]
FROM TABLE_2)
, CTE3 AS (
SELECT ID, [DATE1], [DATE2], [DATE3]
FROM CTE
PIVOT(MAX([DATE]) FOR RN IN ([DATE1],[DATE2],[DATE3])) PIV)
, CTE4 AS (
SELECT ID, [PROD1], [PROD2], [PROD3]
FROM CTE2
PIVOT(MAX(PRODUCT) FOR RN IN ([PROD1],[PROD2],[PROD3])) PIV)
SELECT A.ID, [PROD1], [DATE1], [PROD2], [DATE2], [PROD3], [DATE3]
FROM CTE3 AS A
JOIN CTE4 AS B
ON A.ID=B.ID