求一个sql查询语句 - MS-SQL Server / 基础类

A， id, attributes
  1， 1001,12;1002,45
  2, 1002,14;1003,54
B, id,name
  1001,无敌
  1002，弱
  1003，强悍
A中attributes字段是有多个B数据表的id和值组成的，

现在我想在select,的时候把他的name和值一起显示；
  1，无敌,12;弱,45
  2, 弱,14;强悍,54

语句该怎么写

---

SQL code:
left join

---

SQL code:
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[attributes] [nvarchar](20))
INSERT INTO [A]
SELECT '1','1001,12;1002,45' UNION ALL
SELECT '2','1002,14;1003,54'

--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([id] [int],[name] [nvarchar](10))
INSERT INTO [B]
SELECT '1001','无敌' UNION ALL
SELECT '1002','弱' UNION ALL
SELECT '1003','强悍'

--SELECT * from [A]
--SELECT * from [B]

-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](
@S VARCHAR(200)
)RETURNS VARCHAR(200)
AS
BEGIN
SELECT @S=REPLACE(@S,LTRIM([id]),NAME) from B
RETURN @S
END
GO

SELECT id,[attributes]=dbo.[fn_test]([attributes]) from A
/*
id