求一条sql 话语

求一条sql 语句
sql 版本:2008
表1 FilmInfo (filmBaseInfoid为FilmBaseInfo id)
id filmBaseInfoid maketype  
1 1 3D
2 1 2D
3 1 IMAX
4 2 2D
5 2 IMAX
6 3 3D
表2 FilmBaseInfo
id filmName  
1 变形金刚3
2 阿凡达
3 笔仙

要显示的结果为 :
id filmName maketype
1 变形金刚3 2D/3D/IMAX
2 阿凡达 2D/IMAX 
3 笔仙 3D



PS:如果还有疑问,请留言

[解决办法]

SQL code

IF OBJECT_ID('FilmInfo') Is Not Null   Drop Table FilmInfoCreate Table FilmInfo (ID Int, filmBaseInfoid Int, MakeType Varchar(10))Insert Into FilmInfoSelect 1, 1, '3D'Union All Select 2, 1, '2D'Union All Select 3, 1, 'IMAX'Union All Select 4, 2, '2D'Union All Select 5, 2, 'IMAX'Union All Select 6, 3, '3D'IF OBJECT_ID('FilmBaseInfo') Is Not Null  Drop Table FilmBaseInfoCreate Table FilmBaseInfo (ID Int, FilmName Varchar(20))Insert Into FilmBaseInfoSelect 1, '变形金刚3'Union All Select 2, '阿凡达'Union All Select 3, '笔仙'Select A.ID, A.FilmName, B.MakeTypes From FilmBaseInfo A Left Join (SELECT DISTINCT filmBaseInfoid,    STUFF(             (                 SELECT '/'+MakeType                 FROM FilmInfo t                 WHERE filmBaseInfoid = FilmInfo.filmBaseInfoid FOR XML PATH('')             ), 1, 1, ''         ) AS MakeTypesFROM FilmInfo) B On A.ID = B.filmBaseInfoid /*ID          FilmName             MakeTypes----------- -------------------- ----------------1           变形金刚3                3D/2D/IMAX2           阿凡达                  2D/IMAX3           笔仙                   3D*/
[解决办法]
SQL code
IF OBJECT_ID('FilmInfo') Is Not Null   Drop Table FilmInfoCreate Table FilmInfo (ID Int, filmBaseInfoid Int, MakeType Varchar(10))Insert Into FilmInfoSelect 1, 1, '3D'Union All Select 2, 1, '2D'Union All Select 3, 1, 'IMAX'Union All Select 4, 2, '2D'Union All Select 5, 2, 'IMAX'Union All Select 6, 3, '3D'IF OBJECT_ID('FilmBaseInfo') Is Not Null  Drop Table FilmBaseInfoCreate Table FilmBaseInfo (ID Int, FilmName Varchar(20))Insert Into FilmBaseInfoSelect 1, '变形金刚3'Union All Select 2, '阿凡达'Union All Select 3, '笔仙';WITH TB AS (    SELECT A.ID,A.FilmName,B.MakeType FROM FilmBaseInfo A INNER JOIN FilmInfo B ON A.ID = B.filmBaseInfoid)select id,FilmName, [MakeTypeS]=stuff((select ','+[MakeType] from TB t where id=tb.id for xml path('')), 1, 1, '') from TB group by id ,FilmName/*id          FilmName             MakeTypeS----------- -------------------- ----------------------------------------------------------------------------------------------------------------1           变形金刚3                3D,2D,IMAX2           阿凡达                  2D,IMAX3           笔仙                   3D(3 行受影响)*/