Update several columns with latest values from another table

Question

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 product collected 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.

Answer 1

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

Answer 2

Construction:

WITH ranked AS (
    SELECT [id]
          ,[date]
          ,[product]
          ,row_number() over (partition by id order by date desc) rn
      FROM [sistemy].[dbo].[TABLE_2]
)
SELECT id, [prod1],[date1],[prod2],[date2],[prod3],[date3]  
FROM 
(
    SELECT id, type+cast(rn as varchar(1)) col, value
    FROM ranked
    CROSS APPLY 
    (
        SELECT 'date', CAST([date] AS varchar(8))
        UNION ALL
        SELECT 'prod', product
    ) ca(type, value)
) unpivoted 
PIVOT
(
    max(value)
    for col IN ([prod1],[date1],[prod2],[date2],[prod3],[date3])
) pivoted

You need to take a few steps to achive the aim.

Rank your products by date:

SELECT [id]
  ,[date]
  ,[product]
  ,row_number() over (partition by id order by date desc) rn
FROM [sistemy].[dbo].[TABLE_2]

Unpivot your date and product columns into one column. You can use UNPIVOT OR CROSS APPLY statements. I prefer CROSS APPLY

SELECT id, type+cast(rn as varchar(1)) col, value
FROM ranked
CROSS APPLY 
(
    SELECT 'date', CAST([date] AS varchar(8))
    UNION ALL
    SELECT 'prod', product
) ca(type, value)

or the same result using UNPIVOT

SELECT id, type+cast(rn as varchar(1)) col, value
FROM (
    SELECT [id], 
        rn, 
        CAST([date] AS varchar(500)) date, 
        CAST([product] AS varchar(500)) prod 
    FROM ranked) t
UNPIVOT 
(
    value FOR type IN (date, product)
) unpvt

and at last you use PIVOTE and get a result.