Microsoft SQL Server , view transformation

Question

Is it possible to transform the following view :

to this structure ?

I've tried with cross join but I have no idea how to make a condition based on column name.

Answer 1

You need APPLY instead of JOIN to be able to access outer columns

SELECT t.Date, v.[1], v.[2], v.number
FROM Table t
CROSS APPLY (VALUES
    (t.[1], CAST(NULL AS int), 1),
    (NULL, t.[2], 2)
) v ([1], [2], number)