CODEWITHSUNDEEP
sqlgoogle-bigquery
xerac
xerac

Reputation: 195

How can I put the column values as column headers in Big Query

I have a bigquery table that looks like this:

date          name     grade
2021-02-01   John       7.5
2021-02-01   Martha     8.6
2021-02-01   Peter      5.5
2021-02-02   John       8.0
2021-02-03   John       7.0
2021-02-04   Peter      4.0
2021-02-04   Martha     9.1
2021-02-04   Paul       6.3
2021-02-05   John       7.1
2021-02-05   Mary       1.5
2021-02-06   John       3.3
2021-02-07   Martha     1.8

I would like to create a new table, but that has as columns the names of each person that column names has.

Like this:

date          John     Martha     Peter     Paul     Mary
2021-02-01     7.5      8.6        5.5      null     null
2021-02-02     8.0      null       null     null     null
2021-02-03     7.0      null       null     null     null
2021-02-04     null     9.1        4.0      6.3      null
2021-02-05     7.1      null       null     null     1.5
2021-02-06     3.3      null       null     null     null
2021-02-07     null     1.8        null     null     null

What I have tried is this:

select date,
case when name = 'John' then grade end as John,
case when name = 'Martha' then grade end as Martha,
case when name = 'Peter' then grade end as Peter,
case when name = 'Paul' then grade end as Paul,
case when name = 'Mary' then grade end as Mary
from mytable
order by date

But what I get is:

    date          John     Martha     Peter     Paul     Mary
    2021-02-01    7.5       null      null      null     null
    2021-02-01    null      8.6       null      null     null
    2021-02-01    null      null      5.5       null     null
    2021-02-02    8.0       null      null      null     null
    2021-02-03    7.0       null      null      null     null
    2021-02-04    null      null      4.0       null     null
    2021-02-04    null      9.1       null      null     null
    2021-02-04    null      null      null      6.3      null
    2021-02-05    7.1       null      null      null     null
    2021-02-05    null      null      null      null     1.5
    2021-02-06    3.3       null      null      null     null
    2021-02-07    null      1.8       null      null     null

Upvotes: 0

Views: 694

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

Use aggregation:

select date,
       max(case when name = 'John' then grade end) as John,
       max(case when name = 'Martha' then grade end) as Martha,
       max(case when name = 'Peter' then grade end) as Peter,
       max(case when name = 'Paul' then grade end) as Paul,
       max(case when name = 'Mary' then grade end) as Mary
from mytable
group by date
order by date

Upvotes: 1

Related Questions