I have a table that looks like below:

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:

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

CodePudding user response：

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.

CodePudding user response：

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.