Create Surrogate Keys, from 2 tables

Question

I need to create surrogate key and add it as a new column to the tables My case is :

Table1:

Product_ID	Product_Unique_ID	NEW COLUMN with surrogate key
123	123-aaa	123-123-aaaa
123	123-bbb	123-123-bbb
456	null	456
789	789-aaa	789-789-aaaa

contrain is that in Table2 I have only Product_Id without Product Unique ID so how can i create surrogate key for it? if for case 123 there are 2 unique ids ?

Answer 1

First, for each Product_ID one surrogate key is to be set. One method is to group table1 by Product_ID and use an aggregation for the surrogate key. This can be any_value, min, max or even array_agg.

WITH
 tbl AS (
 SELECT
 Product_ID,
 NULLIF(Product_ID,"456") || "-" || TEMP AS Product_Unique_ID 
 FROM
 UNNEST(SPLIT("123,456,789")) AS Product_ID WITH OFFSET,
  UNNEST(SPLIT("aaa,bbb")) AS TEMP
 WHERE OFFSET =0 OR TEMP="aaa" ),
 tbl1 AS (
 SELECT *, Product_ID || IFNULL("-"||Product_Unique_ID,"") AS surrogate_key
 FROM tbl),
 tbl2 AS (SELECT *, rand() as some_value_tbl2 FROM UNNEST(SPLIT("123,456,789")) as Product_ID),
 tbl1_grp AS ( SELECT Product_ID, ANY_VALUE(surrogate_key) as surrogate_key FROM tbl1 GROUP BY 1 )

SELECT
 *
FROM tbl2
LEFT JOIN tbl1_grp
USING(Product_ID)