SQL Query/stored Procedure One to Many

Question

I hope someone can help as I have been struggling with this for some time.

Expanding on a question I have asked previously

https://learn.microsoft.com/en-us/answers/questions/1257532/sql-query-stored-procedure-one-to-many

my code for retrieving product colors is below

I am now trying to search for Products that has a specific color lets say Blue.

My issue is that the query returns the correct item but only returns the color Blue not any other colors associated with the product.

my basic code to test this is below

My question is how can I do a filter for the color Blue but return 'Blue, Green, Red, Null, Null' as above.

Thank you 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')

,(4,'Red'),(4,'Blue')

;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

WHERE ProductId in (Select ProductId from cteColor where Color='Blue' )

Group by ProductId

drop table ProductColorTable