求连接字典表语句

[bigmingming]

ID CODE
1 00000002,00000003,00000004

表B 字典表

CODE Name
00000002 AAA
00000003 BBB
00000004 CCC


想连接后显示成

ID CODE Name
1 00000002,00000003,00000004 AAA,BBB,CCC

[xxfvba]

select A.*,stuff((select ','+Name from B where charindex(b.Code,a.code)>0 for xml path('')),1,1,'') as [Name] from A

[xxfvba]

select A.*,stuff((select ','+Name from B where charindex(','+b.Code+',',','+a.code+',')>0 for xml path('')),1,1,'') as [Name] from A

[xxfvba]

select A.*,B.[Name] from A, (select distinct stuff((select ','+code from B for xml path('')),1,1,'') as Code,stuff((select ','+name from B for xml path('')),1,1,'') as [Name] from B) B where A.Code=B.Code

[Pact_Alice]

CREATE TABLE # ( ID INT, CODE VARCHAR(30) ) INSERT INTO # SELECT 1,'00000002,00000003,00000004' CREATE TABLE #t4 ( ID INT, CODE VARCHAR(30) ) --定义变量 DECLARE @sql VARCHAR(30), @i INT --给变量赋值 SET @sql=(SELECT CODE + ',' FROM #) SET @i =(SELECT ID FROM #) WHILE Charindex(',', @sql) > 0 BEGIN -- INSERT INTO #t4 SELECT @i, LEFT(@sql, Charindex(',', @sql) - 1) SET @sql= RIGHT(@sql, Len(@sql) - Charindex(',', @sql)) END ;WITH cet AS ( SELECT '00000002' AS CODE ,'AAA'AS NAME UNION ALL SELECT '00000003' AS CODE ,'BBB'AS NAME UNION ALL SELECT '00000004' AS CODE ,'CCC'AS NAME ),cet2 AS ( SELECT t.ID, c1.CODE, c1.NAME FROM cet c1 LEFT JOIN #t4 t ON c1.CODE=t.CODE ) SELECT ID,(STUFF((SELECT ','+NAME FROM cet2 c2 WHERE c2.ID=c.ID FOR XML PATH('') ),1,1,''))AS NAME, (STUFF((SELECT ','+CODe FROM cet2 c2 WHERE c2.ID=c.ID FOR XML PATH('') ),1,1,''))AS Code FROM cet2 c GROUP BY ID

[bigmingming]

select A.*,B.[Name] from A, (select distinct stuff((select ','+code from B for xml path('')),1,1,'') as Code,stuff((select ','+name from B for xml path('')),1,1,'') as [Name] from B) B where A.Code=B.Code 有问题，字典表如何增加到 SELECT '00000002' AS CODE ,'AAA'AS NAME UNION ALL SELECT '00000003' AS CODE ,'BBB'AS NAME UNION ALL SELECT '00000004' AS CODE ,'CCC'AS NAME UNION ALL SELECT '00000005' AS CODE ,'DDD'AS NAME