BigQuery Standard SQL Pivot Rows to Columns

Question

Im trying to query data that uses rows to store time series data using Standard SQL in BigQuery. Example data below. There will be way more Jobs than A-D

+-----+------------+--------------+-----------+
| Job | BatchDate  | SuccessCount | FailCount |
+-----+------------+--------------+-----------+
| A   | 2018-01-01 |           35 |         1 |
| A   | 2018-01-07 |           13 |         6 |
| B   | 2018-01-01 |           12 |        23 |
| B   | 2018-01-07 |           67 |        12 |
| C   | 2018-01-01 |            9 |         4 |
| C   | 2018-01-07 |           78 |         6 |
| D   | 2018-01-01 |            3 |        78 |
| D   | 2018-01-07 |           99 |         5 |
+-----+------------+--------------+-----------+

I would like to have the following as output but cannot work out how to accomplish this in BigQuery.

SuccessCount values in column

+-----+------------+--------------+
| Job | 2018-01-01 | 2018-01-07   |
+-----+------------+--------------+
| A   |         35 |           13 |
| B   |         12 |           67 |
| C   |          9 |           78 |
| D   |          3 |           99 |
+-----+------------+--------------+

Is this sort of thing possible with BigQuery? Can anyone provide a working example?

Thanks

Edit

The data will grow over time, with new entries for each job per week. Is there a way to do this without having to hard code each BatchDate as a column?

If your data really has 8 rows and four of them have no `Job`, then this cannot be done. SQL tables represent *unordered* sets, so there is no way to determine the `Job` on the rows that are missing. — Gordon Linoff, Oct 03 '18 at 10:38

score 1 · Answer 1 · answered Oct 03 '18 at 10:40

1

If the Job is available on all rows, then conditional aggregation does what you want:

select job,
       sum(case when batchdate = '2018-01-01' then SuccessCount else 0 end) as success_20180101,
       sum(case when batchdate = '2018-01-07' then SuccessCount else 0 end) as success_20180107
from t
group by job
order by job;

answered Oct 03 '18 at 10:40

Gordon Linoff

1,242,037
58
646
786

Looking for a solution that allows me to not have hard coded columns if possible – Matt Oct 03 '18 at 10:54

score 0 · Answer 2 · answered Oct 03 '18 at 10:42

0

use case when

    select Job,
   sum(case when batchdate='2018-01-01' then SuccessCount else 0 end) as s_01
    sum(case when batchdate = '2018-01-07' then SuccessCount else 0 end) as s_07
   from t group by job

answered Oct 03 '18 at 10:42

Zaynul Abadin Tuhin

31,407
5
33
63

Looking for a solution that allows me to not have hard coded columns if possible – Matt Oct 03 '18 at 10:53

BigQuery Standard SQL Pivot Rows to Columns

2 Answers2