CODEWITHSUNDEEP
mysql
Yaroslav
Yaroslav

Reputation: 6544

split columns into rows, not classical pivot

EDIT: asked on meta and decided to modify my question, new additions in bold.

I have the following query, that finds overlapping periods of time:

SELECT t1.id1, t2.id1
  FROM `mytable` AS t1
  JOIN `mytable` AS t2
    ON (
        ((t1.start_date BETWEEN t2.start_date AND t2.end_date))
        OR ((t1.end_date BETWEEN t2.start_date AND t2.end_date))
       )
 WHERE t1.id1 < t2.id1;

The result set is:

id1    id1
 6      7
 8      9
 5      11
 10     12

I need the following result, just adding all the values on a single column, and adding a column to show wich rows are pairs so later I can use that result here. Should be simple, but I can not get it working.

col    pair_id
 6       1
 8       2
 5       3
 10      4
 7       1
 9       2
 11      3
 12      4

Upvotes: 0

Views: 239

Answers (2)

Marc B
Marc B

Reputation: 360762

Not sure if this'd work, but try:

SELECT t1.id1
... snip ...
WHERE t1.id1 != t2.id1;

This just explicitly excludes self-overlaps, and would allow the '7' record to show up as an overlap on the '6' (as per your sample results).

Upvotes: 1

devanand
devanand

Reputation: 5290

It's a standard functionality in sql called "union" Take a look in the documentation of the database system you are using.

Upvotes: 1

Related Questions