Unpivot pairs of associated columns to rows

Question

I have a table with staggered article prices like this:

ArtNr   Amount1   Price1   Amount2   Price2   Amount3   Price3
--------------------------------------------------------------
4711          1      3.5         5      3.0        10      2.5
4712          1      5.0         3      4.5         5      4.0
4713          1      7.0        10      6.0       100      5.0

I want to transpose that into this structure:

ArtNr   Amount   Price
----------------------
4711         1     3.5
4711         5     3.0
4711        10     2.5
4712         1     5.0
4712         3     4.5
4712         5     4.0
...

Can this be done with PIVOT/UNPIVOT in T-SQL, or do I have to use UNION?

Answer 1

CROSS APPLY (VALUES is the easiest way to unpivot usually, especially with multiple columns

SELECT
  t.ArtNr,
  v.Amount,
  v.Price
FROM YourTable t
CROSS APPLY (VALUES
  (Amount1, Price1),
  (Amount2, Price2),
  (Amount3, Price3)
) v(Amount, Price)

Some more tricks you can do with CROSS APPLY