在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屋！