Solved

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

Posted on 2014-12-29
8
175 Views
Last Modified: 2015-01-23
I have a set of data and I want to create pairs from each adjacent row. Pairs are only relevant if they have the same id eg

ID, sequence, type
1, 1, a
1, 2, b
1, 3, c
2, 4, d
2, 5, e
2, 6, f

create a set of rows as follows
1,a,b
2,b,c
3,d,e
4,e,f
0
Comment
Question by:pcomb
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40522771
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

Open 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
/

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40523046
>>"Pairs are only relevant if they have the same id"

I cannot follow how that statement works with the examples

there are 3 rows with an ID of 1, and 3 rows with an id of 2 making only 2 result rows

If the quote above is true; how on earth do you get any more than 2 rows as a result
0
 

Author Comment

by:pcomb
ID: 40523092
slightwv thank you this looks good. I got it working for Oracle but Sql Server 2005 does not support lead
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40523457
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.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:pcomb
ID: 40523470
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
0
 
LVL 31

Expert Comment

by:awking00
ID: 40523634
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
;
0
 
LVL 31

Expert Comment

by:awking00
ID: 40523639
Perhaps I should have named the newid alias to newsequence instead, but the query will, in fact, return 1,2,3,4.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40525020
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
;

Open in new window


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

no points please
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Create calculation and case in query with times 13 13
select over clause 1 15
Merge Statement 3 9
SQL Query 3 0
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now