Transpose Sets Of Data In JOIN Between Two Tables

possibilities 41 Reputation points

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 :)


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,643 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points

    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
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. possibilities 41 Reputation points

    I thank you so much for answering me.

    0 comments No comments