Flat Big Query Rows

Question

Please help me to resolve this task. I have Google Big Query table like this:

| name | startDate  | endDate    |
| Bob  | 2018-01-01 | 2018-01-01 |
| Nick | 2017-12-29 | 2017-12-31 |

and as a result I need to get something like this:

| name  | date       |
| Bob   | 2018-01-01 |
| Nick  | 2017-12-29 |
| Nick  | 2017-12-30 |
| Nick  | 2017-12-31 |

Is it possible? Thank you in advance.

score 2 · Answer 1 · answered Nov 07 '18 at 10:41

WITH CTE as (
SELECT 'bob' name, date('2018-01-01') startDate, date('2018-01-01') endDate
UNION ALL SELECT 'Nick', date '2017-12-29' startDate, date('2017-12-31') endDate
),

CTE2 AS (
SELECT name, GENERATE_DATE_ARRAY(startDate, endDate, INTERVAL 1 DAY) AS date
FROM CTE
)

SELECT name, date
FROM CTE2,
UNNEST(date) as date

score 1 · Accepted Answer · answered Nov 07 '18 at 18:11

1

Or just simply

#standardSQL
SELECT name, date
FROM `project.dataset.table`, 
UNNEST(GENERATE_DATE_ARRAY(startDate, endDate)) date

answered Nov 07 '18 at 18:11

Mikhail Berlyant

165,386
8
154
230

score 0 · Answer 3 · answered Nov 07 '18 at 10:27

You may make use of a calendar table here:

WITH dates AS (
    SELECT '2017-12-29' AS date_val UNION ALL
    SELECT '2017-12-30' UNION ALL
    SELECT '2017-12-31' UNION ALL
    SELECT '2018-01-01'
    -- and maybe other dates
)

SELECT
    t2.name,
    t1.date_val
FROM dates t1
INNER JOIN yourTable t2
    ON t1.date_val BETWEEN t2.startDate AND t2.endDate
ORDER BY
    t2.name,
    t1.date_val;

If your version of BigQuery does not support CTE, you may just inline the CTE as a subquery. That is, replace dates with the body of the CTE itself.

In practice, you might want to generate a date series (q.v. here), or possibly maintain a dedicated calendar table in your database. The above just shows what the query itself might look like.

Flat Big Query Rows

3 Answers3

Linked