SQL 合并列问题研究

最新推荐文章于 2016-10-24 14:24:54 发布

chengmodelong

最新推荐文章于 2016-10-24 14:24:54 发布

阅读量378

点赞数

分类专栏： SQL 文章标签： sql

本文链接：https://blog.csdn.net/chengmodelong/article/details/44486747

版权

SQL 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

现在有两个表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

解决方案

--> 测试数据：[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
*/