How to improve the query to result the data in one row?-CodePudding

The following redshift query is showing the data as screenshot in below:

select 
     distinct id_butaca,
        max(CASE WHEN id_tipoorden = 1 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_compra,
        max(CASE WHEN id_tipoorden = 4 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_devo
from dw_fact_table 
where 
id_butaca = 175044501

How can I remove the empty values and put the values in the same row?

id_butaca,   max_fecha_compra,       max_fecha_devo
175044501   2023-01-09 12:11:04.0   2023-01-09 12:09:55

CodePudding user response：

This will help you to merge two rows in one row.

select id_butaca,
    max(max_fecha_compra) as max_fecha_compra,
    max(max_fecha_devo) as max_fecha_devo
from (
        select distinct id_butaca,
            max(
                CASE
                    WHEN id_tipoorden = 1 THEN fechahoracompra
                    ELSE to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                END
            ) over (partition by id_butaca, id_tipoorden) as max_fecha_compra,
            max(
                CASE
                    WHEN id_tipoorden = 4 THEN fechahoracompra
                    ELSE to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                END
            ) over (partition by id_butaca, id_tipoorden) as max_fecha_devo
        from dw_fact_table
        where id_butaca = 175044501
    )
group by id_butaca

CodePudding user response：

Maybe something like this?

select  id_butaca,
        max(CASE WHEN id_tipoorden = 1 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_compra,
        max(CASE WHEN id_tipoorden = 4 THEN fechahoracompra ELSE NULL END) over (partition by id_butaca,id_tipoorden ) as max_fecha_devo
from dw_fact_table 
where id_butaca = 175044501
group by id_butaca