Output two rows from the same row with special numbering

I have a table Products

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

I need an output like this

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

I have this query but getting error near on WHERE clause “The multiple part identifier p1.id could not be bound”. Also not sure how to do Numbering, it is up to 10 per product and company, and the 2020 is extracted from the date. It seems CROSS APPLY might solve the main problem and numbering might be solved with ROW_NUMBER() but I cant wrap my head around it yet.

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

In SQL Server, you can unpivot simply and efficiently with 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)

You can generate the sequence number with 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)

Advertisement

Answer