SQL Query/stored Procedure One to Many

Question

I am undertaking a learning excersise on sql and database design and I understand that you should avoid duplicate data. So in my database I have a product that can have up to 5 colors, therefore to avoid having 5 Columns representing each color I have an intermediate "ProductColorTable" Which is linked to the respective Primary Keys in the ColorTable and ProductTable

When I run a query the Products are repeated based upon the number of Colors referenced within the "ProductColorTable" in relation to the "ProductId" as below.

The Solution I require is

How would I achieve this? Thank you in advance for your assistance.

Answer 1

Create table ProductColorTable (ProductId int, Color varchar(20) )

Insert into ProductColorTable

values(1,'Red'),(1,'Blue'),(1,'Green')

,(2,'Red')

,(3,'Yellow'),(3,'Green')

;with cteColor as (

Select ProductId, Color,row_number() over(partition by productid order by color) rn

from ProductColorTable)

select ProductId,

Max(Case when rn=1 then Color else null end) Color1,

Max(Case when rn=2 then Color else null end) Color2,

Max(Case when rn=3 then Color else null end) Color3,

Max(Case when rn=4 then Color else null end) Color4,

Max(Case when rn=5 then Color else null end) Color5

from cteColor

Group by ProductId

drop table ProductColorTable