divide one row record into 2 rows in sql server

Question

In my Sql table, I want to divide a row record into 2 rows. sample data set given below.

Need to divide 2 rows like this.

This is the query I used

SELECT FRCS2.[CID],FRCS2.[DATE],FRCS2.[Status], 
                    FRCS1.[ID],FRCS1.[DATE],FRCS1.[Status]
FROM #temp FRCS1
INNER JOIN #temp FRCS2
ON FRCS1.[ID] = FRCS2.[ID]

Please help me to solve this. Thanks.

question from:https://stackoverflow.com/questions/65626105/divide-one-row-record-into-2-rows-in-sql-server

Answer 1

In SQL Server, I would recommend apply:

select v.*
from t cross apply
     (values (id1, date1, status1), (id2, date2, status2)
     ) v(id, date, status);

Note that column names cannot be duplicated in a table. So, this assumes that the columns actually have different names.

This approach is preferred over union all because it only scans the table once. For a small table, the performance difference is negligible. It is noticeable for larger tables and can be quite significant if the "table" is really a subquery, CTE, or view.