Return multiple rows result into one row record

I have n:m table which links data between two tables:

id | category 
-------------
2  | car
3  | bike

id | brand
-------------
4  | honda
5  | bmw
6  | mazda
7  | harley davidson

and n:m table

id | pk_category | pk_brand
-----------------------------
1  | 2 (car)     | 4 (honda)
2  | 3 (bike)    | 4 (honda)
3  | 2 (car)     | 6 (mazda)
4  | 3 (bike)    | 7 (harley)

What i need to is create select for table brand. But I want to include categories for every brand. If I do JOIN then I will have two records for Honda

Also imagine the there are 8 categories and over 500 brands.

So is there a way to create select * from brands which will return table like this:

id | brand    | categories
--------------------------
4  | honda    | 2,3 (or different separator)
5  | bmw   开发者_运维知识库   | null
6  | mazda    | 2
7  | harley   | 3

I am really struggling to create something like that. I want to query 50+ brands for one page and dont want to run separate selects for every brand record.

---

;WITH brands(id,brand) AS
(
SELECT 4,'honda' UNION ALL
SELECT  5,'bmw' UNION ALL
SELECT 6,'mazda' UNION ALL
SELECT 7,'harley davidson'
),
brand_categories(id,pk_category,pk_brand)AS
(
SELECT 1,2,4 UNION ALL
SELECT 2,3,4 UNION ALL
SELECT 3,2,6 UNION ALL
SELECT 4,3,7
)
SELECT id,
       brand,
       LEFT(categories, LEN(categories) - 1) AS categories
FROM   brands b
       CROSS APPLY (SELECT CAST(pk_category as varchar) + ','
                    FROM   brand_categories bc
                    WHERE  bc.pk_brand = b.id
                    FOR XML PATH('')) ca(categories) 

Returns

id          brand           categories
----------- --------------- ------------
4           honda           2,3
5           bmw             NULL
6           mazda           2
7           harley davidson 3