Unpivot pairs of associated columns to rows-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

You can only unpivot into 1 column, not 2. You could do this by unpivoting Amount* into Amount, but Price1 - Price5 would stay on each row if selected. So you could unpivot Amount* to AtrNr, Amount, and then join that to unpivoted Price* as ArtNr, Price.

However, it might be easier to read as 5 simple unions. I would suspect the performance is not that different either way, but that's something you will have test and decide on your own. (I haven't tested it myself as I have no idea of the data volume.) If performance is at all acceptable I would go with 5 unions, as I believe that that is easier for a wide range of developers to read (unpivot isn't that common) and thus it gets quite a few more points for maintainability. YMMV.

LEASE NOTE that had not seen Charlieface's answer when I posted mine, and did not know about CROSS APPLY. That looks like the best answer and I have upvoted it.