Solved

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

Posted on 2014-12-29
8
177 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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
 

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 32

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 32

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

821 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