Multiple self- join query on same id group by distinct id

Question

I have this table

sku	product	Qty	Price
1	3M TAPE	1	5.50
1	3M TAPE	10	4.00
1	3M TAPE	20	3.25
4	Scotch	10	6.00
4	Scotch	15	5.50
4	Scotch	20	4.75

I want a self-joined table that looks like this:

sku	product	Qty_1	Price_1	Qty_2	Price_2	Qty_3	Price_3
1	3M TAPE	1	5.50	10	4.00	20	3.25
4	Scotch	10	6.00	15	5.50	20	4.75

I tried a multiple self join with a group by sku but it does not have the intended result. Thanks for the help.

Answer 1

Schema and insert statements:

 create table testTable(sku int, product varchar(50), Qty int, Price  float);

 insert into testTable values(  1 , '3M TAPE',        1  ,     5.50 );
 insert into testTable values(  1 , '3M TAPE',       10  ,     4.00 );
 insert into testTable values(  1 , '3M TAPE',       20  ,     3.25 );
 insert into testTable values(  4 , 'Scotch',        10  ,     6.00 );
 insert into testTable values(  4 , 'Scotch',        15  ,     5.50 );
 insert into testTable values(  4 , 'Scotch',        20  ,     4.75 );

Query:

 with cte as 
 (
   select *,row_number()over(partition by sku order by null) rn from testTable
 )
 select sku,product,
 max(case when rn=1 then qty end)Qty_1,
 max(case when rn=1 then price end)Price_1, 
 max(case when rn=2 then qty end)Qty_2,
 max(case when rn=2 then price end)Price_2,
 max(case when rn=3 then qty end)Qty_3,
 max(case when rn=3 then price end)Price_3
 from cte
 group by sku, product

Output:

sku	product	Qty_1	Price_1	Qty_2	Price_2	Qty_3	Price_3
1	3M TAPE	1	5.5	10	4	20	3.25
4	Scotch	10	6	15	5.5	20	4.75

db<>fiddle here