SQL字段合并和多表联合查询的有关问题_Sql Server

SQL字段合并和多表联合查询的问题
现在有两个表A，B，

A表：

ID NAME
1 amy
2 wang
3 chris

B表

ID PRICE LOCATION
1 11 aaa
1 22 bbb
2 10 ccc
2 15 eee

B表中最多有两条A.ID=B.ID的数据，我想得到的查询结果为：

ID NAME RESULT1 RESULT2
1 amy 11|aaa 22|bbb
2 wang 10|ccc 15|eee
请问查询语句如果写，多谢各位。

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

SQL code

;with cte  as    (select ID,NAME,[RESULT1]=stuff((select '|'+[RESULT1] from B t where ID=tb.ID for xml path('')), 1, 1, '')) from tb  group by id )a,union all(select ID,NAME,[RESULT2]=stuff((select '|'+[RESULT2] from B t where ID=tb.ID for xml path('')), 1, 1, '')) from tb  group by id )bselect ID,NAME,RESULT1,RESULT2  from cte  left join A on cte.ID=A.id
------解决方案--------------------
SQL code--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GOCREATE TABLE [ta]([ID] INT,[NAME] VARCHAR(5))INSERT [ta]SELECT 1,'amy' UNION ALLSELECT 2,'wang' UNION ALLSELECT 3,'chris'GOIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GOCREATE TABLE [tb]([ID] INT,[PRICE] INT,[LOCATION] VARCHAR(3))INSERT [tb]SELECT 1,11,'aaa' UNION ALLSELECT 1,22,'bbb' UNION ALLSELECT 2,10,'ccc' UNION ALLSELECT 2,15,'eee'GO--> 测试语句：SELECT a.[ID],a.[name],max(case when b.row_id=1 then ltrim([PRICE])+'|'+[LOCATION] else '' end) as [result1],max(case when b.row_id=2 then ltrim([PRICE])+'|'+[LOCATION] else '' end) as [result2]FROM [ta] a,(select *,row_id=row_number() over(partition by id order by id) from tb) as b where  a.[ID]=b.[ID]group by a.[ID],a.[name]/*ID          name  result1          result2----------- ----- ---------------- ----------------1           amy   11|aaa           22|bbb2           wang  10|ccc           15|eee*/