Search code examples
sqlsnowflake-cloud-data-platformunpivotunion-alllateral-join

SQL (Snowflake) - Create duplicate Records changing only a single Field after duplication

I have a table that looks like below:

ID DATE_OPENED DATE_CLOSED STATUS TREATMENT
1 2022-12-05 2022-12-05 Notification Control
2 2022-11-24 2022-11-24V Viewed Control
3 2022-12-11 2022-12-11 Subjecting Control

I want to duplicate each record, but for each duplicate, change the Status field from what already exists in that record to that static string: "all" like below:

ID DATE_OPENED DATE_CLOSED STATUS TREATMENT
1 2022-12-05 2022-12-05 Notification Control
1 2022-12-05 2022-12-05 ALL Control
2 2022-11-24 2022-11-24V Viewed Control
2 2022-11-24 2022-11-24V ALL Control
3 2022-12-11 2022-12-11 Subjecting Control
3 2022-12-11 2022-12-11 ALL Control

Is there an easy way to do this in SQL? Perhaps via a window function?

ROW_NUMBER() OVER(PARTITION BY ID ORDER BY 1) = 1
Solution

  • You can do it easily with a UNION ALL, but using 'ALL' in place of the original Status value.

    SELECT ID, DATE_OPENED, DATE_CLOSED,          STATUS, TREATMENT FROM tab
UNION ALL
SELECT ID, DATE_OPENED, DATE_CLOSED, 'ALL' AS STATUS, TREATMENT FROM tab
ORDER BY ID, STATUS DESC

    Note: Last ORDER BY clause is optional.