在SQL Server数据的每两行合并在一列 [英] Merging every two rows of data in a column in SQL Server
问题描述
我的表结构。
Id UserId EventId
1 1 A
2 1 B
3 1 C
4 1 A
5 1 D
输出我需要..
UserId EventStart EventEnd
1 A B
1 B C
1 C A
1 A D
我想每两行合并成一排,因此,如果第一行有A和第二次有B,则结果表的第一行急症室;乙..
I want every two rows to be merged into a row, so if the first row has A and 2nd has B then the first row of result table has A & B..
我看着PIVOT但无法弄清楚如何得到我想要的..
I have looked into PIVOT but unable to figure out how to get the results I want..
这将是巨大的,如果我能解决这个与其他SQL,如果它在中间层要解决,我使用C#
It would be great if I could solve this with sql else if it has to be solved in the middle layer, I'm using C#
任何帮助表示衷心感谢..
Any help is sincerely appreciated..
谢谢..
推荐答案
假设你有有指定排序ID列,你可以得到你想要使用铅()
(在SQL Server 2012 +)是什么:
Assuming that you have have an id column that specifies the ordering, you can get what you want using lead()
(in SQL Server 2012+):
select userId, eventid as eventstart,
lead(eventid) over (partition by userid order by id) as eventend
from mytable t;
您过滤掉了最后一排,你可以使用子查询做(窗口功能都没有在其中,
子句中允许的):
You are filtering out the last row, which you can do with a subquery (window functions aren't allowed in the where
clause):
select t.*
from (select userId, eventid as eventstart,
lead(eventid) over (partition by userid order by id) as eventend
from mytable t
) t
where eventend is null;
在SQL Server早期版本中,你可以通过其他方式,如相关的同样的效果子查询或交叉适用。下面是一个例子:
In earlier versions of SQL Server, you can get the same effect in other ways, such as a correlated subquery or cross apply. Here is an example:
select t.*
from (select userId, eventid as eventstart,
(select top 1 t2.eventid
from mytable t2
where t2.userid = t.userid and
t2.id > t.id
order by t2.id
) as eventend
from mytable t
) t
where eventend is not null;
这篇关于在SQL Server数据的每两行合并在一列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!