SQL - group by value range between 2 columns

Question

Suppose we have the following table:

CREATE TABLE a (
    id int primary key,
    from int, 
    to int
);

INSERT INTO a (id, from, to) VALUES (1, 1, 3);
INSERT INTO a (id, from, to) VALUES (2, 1, 2);
INSERT INTO a (id, from, to) VALUES (3, 2, 4);

I need to write a query, that will return:

+------------+---------+
|    id      |  value  |
+------------+---------+
|     1      |   1     |
|     1      |   2     |
|     1      |   3     |
|            |         |
|     2      |   1     |
|     2      |   2     |
|            |         |
|     3      |   2     |
|     3      |   3     |
|     3      |   4     |
+------------+---------+

The first column is id itself. The second column should contain all values between from and to, with step 1 between values.

Consider handling issues of data display in the application layer if that's available (e.g. a bit of PHP) — Strawberry, Jan 06 '16 at 12:07

score 2 · Accepted Answer · answered Jan 06 '16 at 12:08

2

You need a table of numbers. How you generate such a table depends on the database you are using. But, for small numbers, you can do it manually:

select t.id, (t.from + n.n) as value
from t join
     (select 0 as n union all select 1 union all select 2 union all select 3
     ) n
     on t.from + n.n <= t.to;

answered Jan 06 '16 at 12:08

Gordon Linoff

1,242,037
58
646
786

I have very small numbers, so I will stick to your answer. Thank you! – nickbusted Jan 06 '16 at 12:22
1

@nickbusted If you need more rows you can use this [answer](http://stackoverflow.com/a/2652051/460557) to generate a larger set. – Jorge Campos Jan 06 '16 at 12:24

SQL - group by value range between 2 columns

1 Answers1