how to make different values of rows into columns in presto sql-CodePudding

I was wondering if it is possible to make each distinct values of rows into different columns in presto

For example, what I want to get results like below form

message_varation_id / first_cv / second_cv / third_cv / fourth_cv

however what I end up getting is like below image

img link

which is full of blank values I don't want, and I am getting four rows (for distinct values, I want rows to be unique by message_variation_id)

for example, what I want to get as output in this case is

message_variation_id | first_cv | second_cv | third_cv | fourth_cv
-------------------------------------------------------------------
9617b279~            | {custom~}| {window=~}|{custom~} | {custom ~}

I used the code like below

select distinct message_variation_id, first_cv, second_cv, third_cv, fourth_cv 
from (
select distinct message_variation_id
    , if(bcc.conversion_behavior_index = 0, bcc.conversion_behavior) first_cv
    , if(bcc.conversion_behavior_index = 1, bcc.conversion_behavior) second_cv
    , if(bcc.conversion_behavior_index = 2, bcc.conversion_behavior) third_cv
    , if(bcc.conversion_behavior_index = 3, bcc.conversion_behavior) fourth_cv
from braze_currents.campaigns_conversion_partitioned bcc
where message_variation_id = '9617b279-f5bd-452d-abca-3263cf7e4651'
)

and the table I used, braze_currents.campaigns_conversion_partitioned looks like below

img2 link

select message_variation_id, conversion_behavior_index, conversion_behavior
from braze_currents.campaigns_conversion_partitioned
limit 10

I think it is because of the null value, but sometimes third_cv or fourth_cv don't have values so I made a query like above. I was thinking of using join query, but I am thinking there might an efficient way. any suggestion would be great, thanks! :)

CodePudding user response：

I think you are looking for a conditional max() (your images are hard to read):

select message_variation_id, 
       max(case when bcc.conversion_behavior_index = 0 then  bcc.conversion_behavior end) as first_cv,
       max(case when bcc.conversion_behavior_index = 1 then bcc.conversion_behavior end) second_cv,
       max(case when bcc.conversion_behavior_index = 2 then bcc.conversion_behavior end) as third_cv,
       max(case when bcc.conversion_behavior_index = 3 then bcc.conversion_behavior end) as fourth_cv
from braze_currents.campaigns_conversion_partitioned bcc
where message_variation_id = '9617b279-f5bd-452d-abca-3263cf7e4651'
group by message_variation_id

CodePudding user response：

I think sql below should work.

select message_variation_id, 
       GROUP_CONCAT(DISTINCT first_cv SEPARATOR '') as first_cv,
       GROUP_CONCAT(DISTINCT second_cv SEPARATOR '') as second_cv,
       GROUP_CONCAT(DISTINCT third_cv SEPARATOR '') as third_cv,
       GROUP_CONCAT(DISTINCT fourth_cv SEPARATOR '') as fourth_cv
from braze_currents.campaigns_conversion_partitioned
group by message_variation_id;