What you want for that is something like this:

CREATE TABLE #Categories

(Cat_ID INT,

Cat_Name VARCHAR(25));

INSERT INTO #Categories

(Cat_ID, Cat_Name)

VALUES (1, 'Cosmetics'),

(2, 'Hair'),

(3, 'Furniture'),

(4, 'Office');

CREATE TABLE #Products

(Prod_ID INT,

Prod_Name VARCHAR(25));

INSERT INTO #Products

(Prod_ID, Prod_Name)

VALUES (1, 'Chair'),

(2, 'Gel');

CREATE TABLE #Product_Categories (Prod_ID INT, Cat_ID INT);

INSERT INTO #Product_Categories

(Prod_ID, Cat_ID)

VALUES (1, 3),

(1, 4),

(2, 1),

(2, 2);

SELECT Prod_Name,

STUFF((SELECT ',' + Cat_Name

FROM #Categories AS C

INNER JOIN #Product_Categories AS PC

ON C.Cat_ID = PC.Cat_ID

WHERE PC.Prod_ID = #Products.Prod_ID

FOR XML PATH(''),

TYPE).value('.[1]', 'varchar(1000)'), 1, 1, '') AS Categories

FROM #Products;

For future reference, it's very helpful on your part if you were to provide table scripts and data like I did. Makes it much easier for us to help you. No big deal in a simple case like this, but it does make it easier.

The way my final query works is:

The outer query just pulls the Prod_Name and Prod_ID from the #Products table. That's simple.

The inline sub-query uses the Prod_ID in the outer query to pull the Cat_Name values from the other two tables (that's the Where clause), then uses a trick with For XML Path to turn the data into a list with commas. If you look up "t-sql for xml path" online, you'll find articles on how to do that and how it works. Then, it finally runs a Stuff() function to get rid of the comma at the beginning of the list.

Sub-queries like this, that use a value from the outer query, are called "correlated sub-queries". You can also search online for that, and you'll find more information about how they work, what they're for, and what rules apply to them.

Does that help?