How create a group variable from the two columns

Question

I have the following table

How can I achieve the following table result:

group1 and group2 should be united in one column.

Answer 1

You can use cross apply to unpivot your dataset:

select g.twogroups, t.year, t.quantity
from mytable t
cross apply (values (t.group1), (t.group2)) as g(twogroups)