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?

Answer 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;

Answer 2

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