用特殊编号从同一行输出两行

Question

我有一种桌子产品

ID Company Product Weight Price Date
-- ------- ------- ------ ----- ----
1  555     blanket  3     10    20201207
2  555     blanket  7     45    20201208
3  555     blanket  8     33    20201208
4  123     pillow   7     66    20200901
5  999     pillow   5     55    20200902

我需要这样的输出

Company|Product|Unit Type|Unit Amount|Numbering
------- ------- --------- ----------  ----
555     blanket weight    3           2020-1
555     blanket price     10          2020-1
555     blanket weight    7           2020-2
555     blanket price     45          2020-2
555     blanket weight    8           2020-3
555     blanket price     33          2020-3
123     pillow  weight    7           2020-1
123     pillow  price     66          2020-1
999     pillow  weight    5           2020-1
999     pillow  price     55          2020-1

我有这个查询，但是在WHERE子句“多个部件标识符p1.id无法绑定”上出现了错误。也不知道如何做编号，它是多达10每产品和公司，并从2020年提取的日期。交叉应用似乎可以解决主要的问题，编号可能用ROW_NUMBER()来解决，但我还无法解决它。

SELECT 
    p1.company, 
    p1.product, 
    'weight', 
    p1.weight,
    (CONVERT(VARCHAR(4), p1.date, 112)) + '-',
FROM Products p1
UNION ALL
SELECT 
    p2.company, 
    p2.product, 
    'price', 
    p2.price,
    (CONVERT(VARCHAR(4), p2.date, 112)) + '-',
FROM Products p2
WHERE p1.id=p2.id
ORDER BY company

Answer 1

在Server中，您可以简单而有效地使用cross apply解除枢轴

select t.company, t.product, x.unit_type, x.amount, t.date
from mytable t
cross apply (values ('weight', weight), ('price', price)) as x(unit_type, amount)

可以使用dense_rank()生成序列号。

select t.company, t.product, x.unit_type, x.amount, 
    concat(year(t.date), '-', dense_rank() over(partition by t.company, t.product, year(t.date) order by t.date, t.id)) as rn
from mytable t
cross apply (values ('weight', weight), ('price', price)) as x(unit_type, amount)

Answer 2

另一种选择是使用UNPIVOT，它可以保持代码非常干净。例如：

SELECT ID, Company, Product, Description, Value, Date
FROM (SELECT * FROM Products) p  
UNPIVOT
(
    Unit_Amount FOR Unit_Type IN (Weight, Price)  
) AS unpvt;  
GO