liniking adjacent rows in MS Sql Server 2005 and Oracle 10g

This works for Oracle and should work for SQL Server.

I returned the sequence so it returns:

  SEQUENCE T N
---------- - -
         1 a b
         2 b c
         4 d e
         5 e f

Select allOpen in new window

Not 1,2,3,4.  In Oracle replacing the outer sequence with rownum will return 1,2,3,4.  Not sure what the SQL Server equivalent is.

select sequence, type, next_type from (
select sequence, type, lead(type) over(partition by id order by sequence) next_type
from tab1
)
where next_type is not null
/

Select allOpen in new window

slightwv thank you this looks good. I got it working for Oracle but Sql Server 2005 does not support lead

Again i don't  follow. The results by slightwv don't  match those provided  in thee question.

Could you check the sample data and results please  or verify that  the results  by slightwv as re the correct  ones for the sample data .

Lead  isn't  available  until   mssql 2012 i believe. Its more complex but can be done - if i knew what  the results should be.

PortletPaul slightwvs results do match except the sequence has gaps which is not critical it just needs to  be unique. The key part is to ahve the pairs eg a,b or b.c as rows

If you know a way to do this in sqlserver 2005 that would be great
 SEQUENCE T N
---------- - -
         1 a b
         2 b c
         4 d e
         5 e f

thanks

This should work on both Oracle and SQL Server, with the caveat that SQL Server requires a subquery alias -
select row_number() over (order by sequence) newid, type1, type2 from
(select t1.id, t1.sequence, t1.type type1, t2.type type2
 from yourtable t1, yourtable t2
 where t1.id = t2.id
   and t1. sequence  + 1 = t2.sequence) as x  ==> "as x" is not needed for Oracle
;

Perhaps I should have named the newid alias to newsequence instead, but the query will, in fact, return 1,2,3,4.

Nice cross-dbms solution awking00!

I would recommend  ANSI inner join syntax

SELECT
      ROW_NUMBER() OVER (ORDER BY sequence) NEWSEQENCE
    , type1
    , type2
FROM (
            SELECT
                  T1.id
                , T1.sequence
                , T1.type TYPE1
                , T2.type TYPE2
            FROM yourtable T1
                  INNER JOIN yourtable T2 ON T1.id = T2.id AND T1.sequence + 1 = T2.sequence
      ) X
;

Select allOpen in new window

(with that alias X just leave out "AS " and then it will work for both platforms)

no points please