Блог

listagg работает, как и ожидалось poll_id=2 .

Как я могу выполнить то же самое для одной строки и нескольких столбцов?

 with list_of_fruits as
(select  1 as poll_id ,'Apple' as first_choice, 'Pear' as second_choice, 'Peach' as third_choice, 'Plum' as fourth_choice from dual union all
select  2 as poll_id ,'Cherry' as first_choice, null as second_choice, null as third_choice, null as fourth_choice from dual union all
select  2 as poll_id ,'Grape' as first_choice, null as second_choice, null as third_choice, null as fourth_choice from dual union all
select  2 as poll_id ,'Kiwi' as first_choice, null as second_choice, null as third_choice, null as fourth_choice from dual union all
select  3 as poll_id ,'Squash' as first_choice, 'Peas' as second_choice, '' as third_choice, null as fourth_choice from dual union all
select  3 as poll_id ,null as first_choice, null as second_choice, null as third_choice, 'Barley' as fourth_choice from dual union all
select  3 as poll_id ,null as first_choice, null as second_choice, 'Oats' as third_choice, null as fourth_choice from dual

)    select poll_id, listagg(first_choice,';') within group (order by poll_id) as sam
    from list_of_fruits
    group by poll_id
 

Желаемый результат

Объедините все строки и столбцы (слева направо, сверху вниз), чтобы каждый идентификатор опроса содержал только одну строку в результирующем наборе. Я хочу игнорировать нули.

 1 Apple;Pear;Peach;Plum
2 Cherry;Grape;Kiwi
3 Squash;Peas;Barley;Oats
 

 with
     list_of_fruits 
            ( poll_id, first_choice, second_choice, third_choice, fourth_choice ) as (
       select  1,'Apple' , 'Pear', 'Peach', 'Plum'   from dual union all
       select  2,'Cherry', ''    , ''     , ''       from dual union all
       select  2,'Grape' , ''    , 'Berry', ''       from dual union all
       select  2,'Kiwi'  , ''    , ''     , ''       from dual union all
       select  3,'Squash', 'Peas', ''     , ''       from dual union all
       select  3,''      , ''    , ''     , 'Barley' from dual union all
       select  3,''      , ''    , 'Oats' , ''       from dual
     ),
     prep ( poll_id, first_choice, second_choice, third_choice, fourth_choice, rn ) as (
       select poll_id, first_choice, second_choice, third_choice, fourth_choice,
              row_number() over (partition by poll_id order by null)
       from list_of_fruits
     )
select poll_id, listagg(fruit, ',') within group (order by rn, choice) as sam
from prep
unpivot ( fruit for choice in (first_choice as 1, second_choice as 2, 
                                             third_choice as 3, fourth_choice as 4))
group by poll_id
order by poll_id  -- ORDER BY is optional
;


   POLL_ID SAM
---------- ------------------------------
         1 Apple,Pear,Peach,Plum
         2 Grape,Berry,Cherry,Kiwi
         3 Squash,Peas,Barley,Oats