Matching data from different master tables using single query

Hi,
Sample data below
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 Final Result

CatId       SizeOptions                 ColorOptions

1   101|Small,102|Medium,103|Large      201|Red,202|Green
2   104|FullSleeve,105|halfSleeve       203|Blue,204|White

Tried method was to get the color option and size option using separate queriesby matching values with master
tables. Would like to know if can it be fetched in this format of key value in a single query?

Thanks in advance

what will be consuming this final result? A report or an api?

If you just want every possible combination then:

SELECT *
FROM @categoryMaster catM
CROSS JOIN @sizeMaster sizM
CROSS JOIN @colorMaster colM

Apologies if my question was not clear.The result is the final data given in the question,ie. get the matching size and color against the category id in sql query . This will be used in the server side code to show the product filters for a website

Nothing in those tables matches up. I don't see how to join or limit any of the rows from each table to each other.

Try.. Hope this helps

;
WITH cte_size
AS (SELECT po.catid,
           po.size code,
           sz.sizeName codeName,
           CAST(po.size AS VARCHAR(20)) + '|' + sz.sizeName x
    FROM @productOptions po,
         @sizeMaster sz
    WHERE po.size = sz.id),
     cte_color
AS (SELECT po.catid,
           po.colorid,
           col.colorName,
           CAST(po.colorid AS VARCHAR(20)) + '|' + col.colorName x
    FROM @productOptions po,
         @colorMaster col
    WHERE po.colorid = col.id)


SELECT a.catid,
       (
           SELECT STUFF(
                           (
                               SELECT DISTINCT
                                      ',' + x
                               FROM cte_size c
                               WHERE c.catid = a.catid
                               FOR XML PATH(''), TYPE
                           ).value('.', 'nvarchar(max)'),
                           1,
                           1,
                           ''
                       )
       ) size,
       (
           SELECT STUFF(
                           (
                               SELECT DISTINCT
                                      ',' + x
                               FROM cte_color c
                               WHERE c.catid = a.catid
                               FOR XML PATH(''), TYPE
                           ).value('.', 'nvarchar(max)'),
                           1,
                           1,
                           ''
                       )
       ) color
FROM @productOptions a
GROUP BY a.catid;

That is right.