Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to merge ranges from different tables

Giving the following 2 tables:

T1
------------------
From | To  | Value
------------------
10   | 20  | XXX
20   | 30  | YYY
30   | 40  | ZZZ


T2
------------------
From | To  | Value
------------------
10   | 15  | AAA
15   | 19  | BBB
19   | 39  | CCC
39   | 40  | DDD

What is the best way to get the result below, using T-SQL on SQL Server 2008?

The From/To ranges are sequential (there are no gaps) and the next From always has the same value as the previous To

Desired result
-------------------------------
From | To  | Value1 |  Value2
-------------------------------
10   | 15  | XXX    |  AAA
15   | 19  | XXX    |  BBB
19   | 20  | XXX    |  CCC
20   | 30  | YYY    |  CCC
30   | 39  | ZZZ    |  CCC
39   | 40  | ZZZ    |  DDD
like image 385
pvieira Avatar asked Apr 06 '12 16:04

pvieira


People also ask

How do you merge ranges in Excel?

Click the first cell and press Shift while you click the last cell in the range you want to merge. Important: Make sure only one of the cells in the range has data. Click Home > Merge & Center.

Can I link 2 tables in Excel?

You can create a relationship between two tables of data, based on matching data in each table. Then you can create Power View sheets and build PivotTables and other reports with fields from each table, even when the tables are from different sources.


2 Answers

First I declare data that looks like the data you posted. Please correct me if any assumptions I have made are wrong. Better would be to post your own declaration in the question so we are all working with the same data.

DECLARE @T1 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T1 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 20, 'XXX'),
  (20, 30, 'YYY'),
  (30, 40, 'ZZZ');

DECLARE @T2 TABLE (
  [From] INT,
  [To] INT,
  [Value] CHAR(3)
);

INSERT INTO @T2 (
  [From],
  [To],
  [Value]
)
VALUES
  (10, 15, 'AAA'),
  (15, 19, 'BBB'),
  (19, 39, 'CCC'),
  (39, 40, 'DDD');

Here is my select query to generate your expected result:

SELECT
  CASE
    WHEN [@T1].[From] > [@T2].[From]
    THEN [@T1].[From]
    ELSE [@T2].[From]
  END AS [From],
  CASE
    WHEN [@T1].[To] < [@T2].[To]
    THEN [@T1].[To]
    ELSE [@T2].[To]
  END AS [To],
  [@T1].[Value],
  [@T2].[Value]
FROM @T1
INNER JOIN @T2 ON
  (
    [@T1].[From] <= [@T2].[From] AND
    [@T1].[To] > [@T2].[From]
  ) OR
  (
    [@T2].[From] <= [@T1].[From] AND
    [@T2].[To] > [@T1].[From]
  );
like image 60
Iain Samuel McLean Elder Avatar answered Sep 19 '22 21:09

Iain Samuel McLean Elder


Stealing @isme's data setup, I wrote the following:

;With EPs as (
    select [From] as EP from @T1
    union
    select [To] from @T1
    union
    select [From] from @T2
    union
    select [To] from @T2
), OrderedEndpoints as (
    select EP,ROW_NUMBER() OVER (ORDER BY EP) as rn from EPs
)
select
    oe1.EP,
    oe2.EP,
    t1.Value,
    t2.Value
from
    OrderedEndpoints oe1
        inner join
    OrderedEndpoints oe2
        on
            oe1.rn = oe2.rn - 1
        inner join
    @T1 t1
        on
            oe1.EP < t1.[To] and
            oe2.EP > t1.[From]
        inner join
    @T2 t2
        on
            oe1.EP < t2.[To] and
            oe2.EP > t2.[From]

That is, you create a set containing all of the possible end points of periods (EPs), then you "sort" those and assign each one a row number (OrderedEPs).

Then the final query assembles each "adjacent" pair of rows together, and joins back to the original tables to find which rows from each one overlap the selected range.

like image 40
Damien_The_Unbeliever Avatar answered Sep 19 '22 21:09

Damien_The_Unbeliever