SQL Server 将多行合二为一 [英] SQL Server combine multiple rows into one
本文介绍了SQL Server 将多行合二为一的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想将多行合并成单行
Inv_ID = 188
、198和82的客户端应该合并为一行
The client with Inv_ID = 188
, 198 and 82 should be merged into one row
结果应该有两行..
ClientID | Input(188) | Input(198 | Input(82) |Date(188) |Date (198)
--------------------------------------------------------------------
133 | Yes | yes | Referred to comm Pres | 2016-08-16 01:00 | 2016-8-01
133 | yes | yes | Referred to comm Pres | 2016-08-17 00:00 | 2016-08-17
有人可以帮我吗?
谢谢.
推荐答案
我认为解决这个问题的关键是了解 188,198 和 88s 属于哪个块.在下面的 cte 中,我工作的基础是 88 总是终止一个块,并将 88 的 rowid 分配给所有小于它的 rowid - 从而启用分组.
I think the key to this is understanding which blocks the 188,198 and 88s fall into. In the cte below i am working on the basis that an 88 always terminates a block and am assigning the rowid of the 88 to all rowids less than it - thus enabling the group by.
declare @t table(client_id int, m_id int,chid int,inv_id int,input varchar(20),dt datetime,rowid int)
insert into @t values
(133,928,9581,188,'yes_b1','2016-08-16 01:00:00:000',1),
(133,929,9581,198,'yes_b1','2016-08-16 01:10:00:000',2),
(133,930,9581,82,'referred_b1','2016-08-16 01:30:00:000',3),
(133,935,9584,188,'yes_b2','2016-08-16 01:00:00:000',5),
(133,936,9584,198,'yes_b2','2016-08-16 01:00:00:000',6),
(133,937,9584,82,'referred_b2','2016-08-16 01:00:00:000',7)
;with cte as
(
select s.*,
lag(s.hi,1,0) over (order by s.inv_id) as lo
from
(
select inv_id,rowid as hi
from @t
where inv_id = 82
)s
)
select t.client_id,
max(case when t.inv_id = 188 then input end) 'input(188)',
max(case when t.inv_id = 198 then input end) 'input(198)',
max(case when t.inv_id = 82 then input end) 'input(82)',
max(case when t.inv_id = 188 then dt end) 'date(188)',
max(case when t.inv_id = 198 then dt end) 'date(198)',
max(case when t.inv_id = 82 then dt end) 'date(82)'
from @t t
join cte on rowid <= cte.hi and rowid > cte.lo
group by client_id,cte.hi
结果
client_id input(188) input(198) input(82) date(188) date(198) date(82)
----------- -------------------- -------------------- -------------------- ----------------------- ----------------------- -----------------------
133 yes_b1 yes_b1 referred_b1 2016-08-16 01:00:00.000 2016-08-16 01:10:00.000 2016-08-16 01:30:00.000
133 yes_b2 yes_b2 referred_b2 2016-08-16 01:00:00.000 2016-08-16 01:00:00.000 2016-08-16 01:00:00.000
这篇关于SQL Server 将多行合二为一的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文