SQL Unpivot multiple columns Data

The answer by Taryn is indeed super useful, and I'd like to expand one aspect of it.

If you have a very un-normalized table like this, with multiple sets of columns for e.g. 4 quarters or 12 months:

+-------+------+------+------+------+------+------+-------+------+
| cYear | foo1 | foo2 | foo3 | foo4 | bar1 | bar2 | bar3  | bar4 |
+-------+------+------+------+------+------+------+-------+------+
|  2020 |   42 |  888 |    0 |   33 | one  | two  | three | four |
+-------+------+------+------+------+------+------+-------+------+

Then the CROSS APPLY method is easy to write and understand, when you got the hang of it. For the numbered column, use constant values.

SELECT 
    cYear,
    cQuarter,
    foo,
    bar
FROM temp

CROSS APPLY
(
  VALUES
    (1, foo1, bar1),
    (2, foo2, bar2),
    (3, foo3, bar3),
    (4, foo4, bar4)

) c (cQuarter, foo, bar)

Result:

+-------+----------+-----+-------+
| cYear | cQuarter | foo |  bar  |
+-------+----------+-----+-------+
|  2020 |        1 |  42 | one   |
|  2020 |        2 | 888 | two   |
|  2020 |        3 |   0 | three |
|  2020 |        4 |  33 | four  |
+-------+----------+-----+-------+

SQL Fiddle

An easier way to unpivot the data would be to use a CROSS APPLY to unpivot the columns in pairs:

select vendorid, orders, orders1
from pvt1
cross apply
(
  select emp1, sa union all
  select emp2, sa1
) c (orders, orders1);

See SQL Fiddle with Demo. Or you can use CROSS APPLY with the VALUES clause if you don't want to use the UNION ALL:

select vendorid, orders, orders1
from pvt1
cross apply
(
  values 
    (emp1, sa),
    (emp2, sa1)
) c (orders, orders1);

See SQL Fiddle with Demo