This is my table.

Here is the expected output:

Please help me how to split like this in BigQuery.

CodePudding user response：

SELECT * EXCEPT(t) REPLACE(t AS start)
  FROM my_table, UNNEST(GENERATE_ARRAY(start, `end`)) t
 ORDER BY Location, start;

Query results:

 ---------- ------------ ---------- ------- ----- 
| Location |    Date    | Employee | start | end |
 ---------- ------------ ---------- ------- ----- 
| A        | 2021-01-01 | A1       |    10 |  15 |
| A        | 2021-01-01 | A1       |    11 |  15 |
| A        | 2021-01-01 | A1       |    12 |  15 |
| A        | 2021-01-01 | A1       |    13 |  15 |
| A        | 2021-01-01 | A1       |    14 |  15 |
| A        | 2021-01-01 | A1       |    15 |  15 |
| A        | 2021-01-01 | A1       |    15 |  16 |
| A        | 2021-01-01 | A1       |    16 |  16 |
| B        | 2021-01-01 | B1       |    16 |  21 |
| B        | 2021-01-01 | B1       |    17 |  21 |
| B        | 2021-01-01 | B1       |    18 |  21 |
| B        | 2021-01-01 | B1       |    19 |  21 |
| B        | 2021-01-01 | B1       |    20 |  21 |
| B        | 2021-01-01 | B1       |    21 |  21 |
| C        | 2021-01-01 | C1       |    11 |  15 |
| C        | 2021-01-01 | C1       |    12 |  15 |
| C        | 2021-01-01 | C1       |    13 |  15 |
| C        | 2021-01-01 | C1       |    14 |  15 |
| C        | 2021-01-01 | C1       |    15 |  15 |
 ---------- ------------ ---------- ------- -----