Product filter building query

Transact-SQL
1

Hi,

Trying to build matching filters for product category against size and color. Have master tables for category,size and color and product options table.

Declare @categoryMaster table (id int,catName varchar(50))

Insert Into @categoryMaster 
 Values (1, 'Caps')
      , (2, 'Shirts')
	  
select * from @categoryMaster

Declare @sizeMaster table (id int,sizeName varchar(50))

Insert Into @sizeMaster 
 Values (101, 'Small')
      , (102, 'Medium')
	   , (103, 'Large')
	    , (104, 'FullSleeve')
		 , (105, 'HalfSleeve')

select * from @sizeMaster

Declare @colorMaster table (id int,colorName varchar(50))
Insert Into @colorMaster 
 Values (201, 'Red')
      , (202, 'Green')
	  , (203, 'Blue')
	  , (204, 'White')
select * from @colorMaster

-- prod options

Declare @productOptions table (prodid int,catid int,size int,colorid int)

Insert Into @productOptions 
 Values 
	(5000,1,101,201)
	,(5001,1,101,202)
	,(5002,1,102,201)
	,(5003,1,103,202)
	,(6000,2,104,203)
	,(6001,2,105,204)

select * from @productOptions

---Expected Result--

CatName SizeFilter ColorFilter

Cap Small|Medium|Large Red|Green
Shirt FullSleeve|FullSleeve Blue|White

2

Not sure if order is required, but

SELECT distinct cmast.catName,
stuff(
( SELECT distinct '|' + s.sizeName
FROM @productOptions p1
join @colorMaster c
on c.ID = p1.colorid
join @categoryMaster cm
on p1.catid = cm.id
join @sizeMaster s
on p1.size = s.id
where cm.CatName = cmast.catName

            FOR XML PATH('') 
    ), 
    1, 
    1,'' 
  ) AS size, 
	stuff( 
    ( SELECT distinct '|' + c.colorName
		FROM  @productOptions p1
			join   @colorMaster c
				on c.ID = p1.colorid
			join @categoryMaster cm
					on p1.catid = cm.id
			join @sizeMaster s
				on p1.size = s.id
		where cm.CatName = cmast.catName
		  
            FOR XML PATH('') 
    ), 
    1, 
    1,'' 
  ) AS color

FROM @productOptions p1
join @colorMaster c
on c.ID = p1.colorid
join @categoryMaster cmast
on p1.catid = cmast.id
join @sizeMaster s
on p1.size = s.id

2 Likes