Solved

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

Posted on 2014-12-29
8
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 77

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 49

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 49

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 49

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

623 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