Redshift – Is there a way to merge rows based on a common column

I have a table like this

Node_id | channel       | timestamp           | value                       | folder_id
242278  | return_status | 2020-07-28 14:29:26 | "Return Created"            | 48486
242278  | return_reason | 2020-07-28 14:29:26 | "Customer Opt Out"          | 48486
242278  | return_date   | 2020-07-28 14:29:26 | "2020-07-28T10:29:26-04:00" | 48486
242281  | return_status | 2020-07-28 15:17:31 | "Return Created"            | 34195
242281  | return_reason | 2020-07-28 15:17:31 | "Customer Opt Out"          | 34195
242281  | return_date   | 2020-07-28 15:17:31 | "2020-07-28T11:17:30-04:00" | 34195
242283  | return_status | 2020-07-28 17:08:07 | "Return Created"            | 48896
242283  | return_date   | 2020-07-28 17:08:07 | "2020-07-28T13:08:06-04:00" | 48896
242283  | return_reason | 2020-07-28 17:08:07 | "Poor quality / Faulty"     | 48896

Is there a way to do this in a single query, or would I need to create a CTE for each different channel and then create a join?

Thanks in advance for all your help! I am trying to create a query that will let me merge the rows into a single row based on the node_id, something like below

Node_id | return_status    | return_reason           | return_date                | folder_id
242278  | "Return Created" | "Customer Opt Out"      | "2020-07-28T10:29:26-04:00"| 48486
242281  | "Return Created" | "Customer Opt Out"      | "2020-07-28T11:17:30-04:00"| 34195
242283  | "Return Created" | "Poor quality / Faulty" | "2020-07-28T13:08:06-04:00"| 48896

Answer

You can do conditional aggregation:

select
    node_id,
    max(case when channel = 'return_status' then value end) return_status,
    max(case when channel = 'return_reason' then value end) return_reason,
    max(case when channel = 'return_date'   then value end) return_date,
    folder_id
from mytable
group by node_id, folder_id

Advertisement

Answer