将多行结果返回到一行记录中

Question

我有n:m表，它在两个表之间链接数据：

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

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

和n:m表

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)

我需要的是为表品牌创建select。但我想包括每个品牌的类别。如果我执行JOIN，那么我将有两条Honda记录

想象一下，有8个类别，500多个品牌。

那么有没有一种方法可以创建这样的返回表的select * from brands：

id | brand    | categories
--------------------------
4  | honda    | 2,3 (or different separator)
5  | bmw      | null
6  | mazda    | 2
7  | harley   | 3

我真的很难创造出这样的东西。我想查询一个页面的50+品牌，不想为每个品牌记录运行单独的选择。

Answer 1

;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) 

返回

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