CODEWITHSUNDEEP
sqlgroup-bypivotsnowflake-cloud-data-platform
mjkaufer
mjkaufer

Reputation: 4216

Dynamic Column Alias Creation & Joining

I'm trying to query some data in snowflake and save myself a bunch of hard-coding

My data rows (let's call this subquery A) look something like this

| my_index  | score | some_enum |
|-----------|-------|-----------|
| abc.      | 100.  | x.        |
| abc.      | 50.   | x.        |
| abc.      | 50.   | y.        |
| abc.      | 60.   | y.        |
| def.      | 90.   | z.        |

I want to group by my_index and test_name, compute average scores, and then join all of this data back together with dynamic column names based on some_enum, so it would look something like

| my_index  | avg_score_x | avg_score_y | avg_score_z | avg_score |
|-----------|-------------|-------------|-------------|-----------|
| abc.      | 75.         | 55.         | 0/NaN/-1.   | 65.       |
| def.      | 0/NaN/-1.   | 0/NaN/-1.   | 90.         | 90.       |

Does anybody have a clean way of dynamically creating these column names and joining this data?

Upvotes: 0

Views: 184

Answers (1)

GMB
GMB

Reputation: 222652

You can do conditional aggregation:

select
    myindex,
    avg(case when some_enum = 'x' then score end) avg_score_x,
    avg(case when some_enum = 'y' then score end) avg_score_y,
    avg(case when some_enum = 'z' then score end) avg_score_z,
    avg(score) avg_score
from a
group by myindex
order by myindex

Upvotes: 1

Related Questions