sql 视图中2表 一对多的关系,该如何处理

sql 视图中2表 一对多的关系
A表 字段
iCwid 
1
2
3

B表 字段
IWKWID iCwid keyword
1 1 abc
2 1 bcd  
3 1 abdc
4 2 ad
5 3 asda

怎样将2个表放到一个视图里C
iCwid keyword
1 abc bcd abdc
2 ad
3 asda


------解决方案--------------------------------------------------------

SQL code

SELECT A.* FROM    (SELECT iCwid, stuff((SELECT ' ' + X.keyword FROM B表 AS X WHERE X.iCwid=Y.iCwid FOR xml path('')), 1, 1, '') AS keyword    FROM B表 AS Y GROUP BY Y.iCwid) AS AINNER JOIN A表 AS BON A.iCwid=B.iCwid