Biegral Blog

2  wang 10|ccc               15|eee

解决方案

--> 测试数据：[ta]
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([ID] INT,[NAME] VARCHAR(5))
INSERT [ta]
SELECT 1,'amy' UNION ALL
SELECT 2,'wang' UNION ALL
SELECT 3,'chris'
GO
 
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([ID] INT,[PRICE] INT,[LOCATION] VARCHAR(3))
INSERT [tb]
SELECT 1,11,'aaa' UNION ALL
SELECT 1,22,'bbb' UNION ALL
SELECT 2,10,'ccc' UNION ALL
SELECT 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|bbb
2           wang  10|ccc           15|eee
*/

方案2

--2005
 
select * from
(
    select a.ID,a.NAME,ltrim(b.PRICE)+'|'+b.LOCATION as result ,
    'result'+ltrim(b.row_id) as row_id
    from 
    [ta] a,
    (select *,row_id=row_number() over(partition by id order by id) from tb) as b 
    where  a.[ID]=b.[ID]
) a
pivot(max(result) for row_id in([result1],[result2])) b
 
/*
ID          name  result1          result2
----------- ----- ---------------- ----------------
1           amy   11|aaa           22|bbb
2           wang  10|ccc           15|eee
*/