CODEWITHSUNDEEP
c#sqlsql-serverlinqlinq-to-sql
Arnab
Arnab

Reputation: 2354

Merging every two rows of data in a column in SQL Server

My table structure is..

Id   UserId EventId
  1      1       A    
  2      1       B
  3      1       C
  4      1       A
  5      1       D

The output I need..

UserId   EventStart   EventEnd
1           A           B
1           B           C
1           C           A
1           A           D

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..

I have looked into PIVOT but unable to figure out how to get the results I want..

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..

Thanks..

Upvotes: 2

Views: 931

Answers (2)

bummi
bummi

Reputation: 27385

An easy approach would be using a CTE with a generated Row_Number() over the ID and joining over UserID and Rownumber.

declare @t  Table([ID] [int] IDENTITY(1,1) NOT NULL, UserID int,EventID varchar(10))
insert into @t
Select 1,'A'
UNION ALL Select 1,'B'
UNION ALL Select 1,'C'
UNION ALL Select 1,'A'
UNION ALL Select 1,'D'
UNION ALL Select 2,'B'
UNION ALL Select 2,'C'
UNION ALL Select 2,'A'
UNION ALL Select 2,'D'


;With c as
(
Select UserID,EventID,Row_Number() OVER (Order by UserID,ID ) as RN
from @t
)
Select c1.UserID,c1.EventID as EventStart ,c2.EventID as EventEnd
from c c1
Join c c2 on c2.RN=c1.RN+1 and c2.UserID=c1.UserID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

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;

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;

Upvotes: 2

Related Questions