ID_DETAILS column from the ab and bc tables as per the image shown

Question

Table: AB

AP_ID   UPDT_BY_ID   UPDT_DATE
9848         923     '2019-09-14 21:09'
3242         427     '2019-09-15 21:09'
7845         514     '2019-09-16 21:09'

Table: BC

AP_ID   CREATED_BY_ID   CREATED_DT
1234     123            '2019-08-21 20:30'
5678     456            '2019-08-22 20:30'
2468     789            '2019-08-23 20:30'

The output table I would like help with: ABC

TABLE_NAME   ID_DETAILS                   SRC_DATE              RUN_DATE
AB           AP_ID:9849,UPDT_BY_ID:923    '2019-09-14 21:09'    '2020-03-28'
AB           AP_ID:3242,UPDT_BY_ID:427    '2019-09-15 21:09'    '2020-03-28'
AB           AP_ID:7845,UPDT_BY_ID:514    '2019-09-16 21:09'    '2020-03-28'
AB           AP_ID:1234,UPDT_BY_ID:123    '2019-08-21 21:09'    '2020-03-28'
AB           AP_ID:5678,UPDT_BY_ID:456    '2019-08-22 21:09'    '2020-03-28'
AB           AP_ID:2468,UPDT_BY_ID:789    '2019-08-23 21:09'    '2020-03-28'

I need to create table: ABC from AB and BC tables in snowflake as per above ID_DETAILS column in ABC table should be in JSON format.

What about using a CTE or union to bring the data together and use the parse_json function to construct the input in the format that will then be converted to VARIANT (which is the datatype you want this ID_DETAILS column to be if it to be stored/unloaded as JSON). There's a good example of using parse_json here: https://docs.snowflake.com/en/sql-reference/functions/to_json.html#examples — Mike Donovan, Mar 28 '20 at 19:47
please don't use pictures for data that can be simply formatted in markdown, as it makes a lot more effort for other to work with you problem. — Simeon Pilgrim, Mar 29 '20 at 22:46

score 0 · Answer 1 · answered Mar 29 '20 at 22:45

WITH tabe_ab AS (
  SELECT * FROM VALUES 
    (9848, 923, '2019-09-14 21:09'::timestamp_ntz),
    (3242, 427, '2019-09-15 21:09'::timestamp_ntz),
    (7845, 514, '2019-09-16 21:09'::timestamp_ntz)
    d(AP_ID, UPDT_BY_ID, UPDT_DATE)
), table_bc AS (
  SELECT * FROM VALUES
    (1234, 123, '2019-08-21 20:30'::timestamp_ntz),
    (5678, 456, '2019-08-22 20:30'::timestamp_ntz),
    (2468, 789, '2019-08-23 20:30'::timestamp_ntz)  
    b(AP_ID, CREATED_BY_ID, CREATED_DT)
), data_union AS (
    SELECT 'AB' as table_name
        ,AP_ID
        ,UPDT_BY_ID
        ,UPDT_DATE as SRC_DATE
    FROM tabe_ab
    UNION ALL
    SELECT 'BC' as table_name
        ,AP_ID
        ,CREATED_BY_ID AS UPDT_BY_ID
        ,CREATED_DT as SRC_DATE
    FROM table_bc
)
SELECT table_name
   ,OBJECT_CONSTRUCT('AP_ID',AP_ID,'UPDT_BY_ID',UPDT_BY_ID) AS id_details
   ,SRC_DATE
   ,CURRENT_DATE
FROM data_union
order by 1,3;

gives the results:

TABLE_NAME  ID_DETAILS  SRC_DATE    CURRENT_DATE
AB  {    "AP_ID": 9848,    "UPDT_BY_ID": 923  } 2019-09-14 21:09:00.000 2020-03-29
AB  {    "AP_ID": 3242,    "UPDT_BY_ID": 427  } 2019-09-15 21:09:00.000 2020-03-29
AB  {    "AP_ID": 7845,    "UPDT_BY_ID": 514  } 2019-09-16 21:09:00.000 2020-03-29
BC  {    "AP_ID": 1234,    "UPDT_BY_ID": 123  } 2019-08-21 20:30:00.000 2020-03-29
BC  {    "AP_ID": 5678,    "UPDT_BY_ID": 456  } 2019-08-22 20:30:00.000 2020-03-29
BC  {    "AP_ID": 2468,    "UPDT_BY_ID": 789  } 2019-08-23 20:30:00.000 2020-03-29

for your actual tables you would just use the CTE section

WITH data_union AS (
    SELECT 'AB' as table_name
        ,AP_ID
        ,UPDT_BY_ID
        ,UPDT_DATE as SRC_DATE
    FROM ab
    UNION ALL
    SELECT 'BC' as table_name
        ,AP_ID
        ,CREATED_BY_ID AS UPDT_BY_ID
        ,CREATED_DT as SRC_DATE
    FROM bc
)

ID_DETAILS column from the ab and bc tables as per the image shown

1 Answers1