0

Is it possible to transform the following view :

enter image description here

to this structure ?

enter image description here

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

inspiredd
  • 195
  • 2
  • 11

1 Answers1

2

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)
Charlieface
  • 52,284
  • 6
  • 19
  • 43