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.

Could you please explain the logic of this transformation? How was 98% converted into 100% — Sergey, Jan 08 '21 at 15:13

score 2 · Accepted Answer · answered Jan 08 '21 at 15:13

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)

answered Jan 08 '21 at 15:13

Charlieface

1 Answers1