2

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

Arnab
  • 2,324
  • 6
  • 36
  • 60

2 Answers2

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have sql server 2008 locally but I will be hosting at sql azure, for testing I'm using correlated subquery- here 'my table name' will replace t or t2? – Arnab Aug 05 '14 at 11:07
  • I think the last part of query should be 'where eventend is not null;' – Arnab Aug 05 '14 at 11:23
  • @Amab . . . I totally misread your comment. My apologies, you are -- of course -- correct. – Gordon Linoff Aug 05 '14 at 11:48
  • any idea why an Or clause does not work inside where clause here..I tried "where eventstart != 'A' " -this worked but when I tried "where eventstart != 'A' Or eventstart != 'B' " - this did not.. – Arnab Aug 05 '14 at 14:52
  • 1
    @Amab . . . I don't know what your question is referring to. But if you want neither A nor B, just use `not in`: `eventstart not in ('A', 'B')`. – Gordon Linoff Aug 05 '14 at 15:04
1

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
bummi
  • 27,123
  • 14
  • 62
  • 101