1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
Blackdynomite
  • 421
  • 1
  • 4
  • 18

2 Answers2

1

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
1

In Snowflake, I would recommend a lateral join to duplicate the rows:

select t.id, t.date_opened, t.date_closed,
    v.status, t.treatment
from mytable t
cross join lateral ( values (t.status), ('ALL') ) v(status)

Not only is the syntax neater; the real upside of this approach is efficiency, as it scans the table only once, as opposed to the union all solution, which scans twice.

GMB
  • 216,147
  • 25
  • 84
  • 135