sql - 使用sql server将单行多列合并为一列

Question

我有这样的 table

OrderNo  Item_Description1  Rate1  Quantity1  Item_Description2  Rate2  Quantity2  Item_Description3  Rate3  Quantity3 
-------- ------------------ ------ ---------- ------------------ ------ ---------- ------------------ ------ ----------
1001     Laptop             50000  8          Air Conditioner    20000  10         Television         25000  12
1002     Washing Machine    35000  10         Camera             4000   20         Speaker            1500   15

由此我需要创建一个临时表或这样的表:

OrderNo  Item_Description   Rate   Quantity 
-------- ------------------ ------ ----------
1001     Laptop             50000  8
         Air Conditioner    20000  10
         Television         25000  12
1002     Washing Machine    35000  10
         Camera             4000   20
         Speaker            1500   15

我可以在 SQL Server 中执行此操作吗？

Answer 1

您还可以使用 CROSS APPLY 对数据进行逆透视:

select t.order_no,
  c.item_description,
  c.rate,
  c.quantity
from yourtable t
cross apply
(
  select item_description1, rate1, quantity1 union all
  select item_description2, rate2, quantity2 union all
  select item_description3, rate3, quantity3
) c (item_description, rate, quantity)