Transpose Sets Of Data In JOIN Between Two Tables

Question

I have an example that I am trying to code in SQL (SSMS). I am trying to join the fruit table to the characteristics table and display the sets (1, 2, 3) of characteristics for each fruit horizontally. Can this be done using SQL? Thank you :)

Answer 1

Try (from the top of my head)

;with cteSource as (select F.*, ch.price, ch.weight, ch.location, ch.qty,
row_number() over (partition by f.Fruit order by ch.qty) as Rn -- for example
from dbo.Fruit F left join dbo.Characterists ch on F.Fruit = Ch.Fruit)

select s.Fruit, s.Color, 
max(case when Rn = 1 then Price end) as Price1,
max(case when Rn = 1 then Location end) as Location1,
max(case when Rn = 1 then Qty end) as Qty1,
max(case when Rn = 2 then Price end) as Price2,
max(case when Rn = 2 then Location end) as Location2,
max(case when Rn = 2 then Qty end) as Qty2,
max(case when Rn = 3 then Price end) as Price3,
max(case when Rn = 3 then Location end) as Location3,
max(case when Rn = 3 then Qty end) as Qty3
from cteSource s
where Rn <=3
GROUP BY s.Fruit, s.Color
Order by s.Fruit, s.Color

Answer 2

I thank you so much for answering me.