I'm trying to move some rows and convert them as columns

Question

can we get the below desired table by using PIVOT or something. I'm trying to convert below table to the desired output like below.

Data Set:

question_id    element_id
1              john
1              bran
1              o_siera
2              brook
2              joseph
2              o_daniel
2              o_cody
3              derick
3              james
3              sophia
3              o_sandra
3              o_ashley

Desired Result:

Can we achieve it in this way

question_id    element_id       element
1              john             o_siera
1              bran             
2              brook            o_daniel,o_cody
2              joseph           
3              derick           o_sandra, o_ashley
3              james            
3              sophia   

Answer 1

Based on your provided sample data, there is no way to know which ELEMENT_ID should have the ELEMENT list next to it.

You can use a query like the one below which is putting the ELEMENT list next to the first ELEMENT_ID alphabetically for each QUESTION_ID.

Example

WITH
    t (question_id, element_id)
    AS
        (SELECT 1, 'john' FROM DUAL
         UNION ALL
         SELECT 1, 'bran' FROM DUAL
         UNION ALL
         SELECT 1, 'o_siera' FROM DUAL
         UNION ALL
         SELECT 2, 'brook' FROM DUAL
         UNION ALL
         SELECT 2, 'joseph' FROM DUAL
         UNION ALL
         SELECT 2, 'o_daniel' FROM DUAL
         UNION ALL
         SELECT 2, 'o_cody' FROM DUAL
         UNION ALL
         SELECT 3, 'derick' FROM DUAL
         UNION ALL
         SELECT 3, 'james' FROM DUAL
         UNION ALL
         SELECT 3, 'sophia' FROM DUAL
         UNION ALL
         SELECT 3, 'o_sandra' FROM DUAL
         UNION ALL
         SELECT 3, 'o_ashley' FROM DUAL)
SELECT question_id,
       element_id,
       CASE
           WHEN ROW_NUMBER () OVER (PARTITION BY question_id ORDER BY element_id) = 1
           THEN
               (SELECT LISTAGG (t2.element_id, ', ')
                  FROM t t2
                 WHERE element_id LIKE 'o\_%' ESCAPE '\' AND t2.question_id = t1.question_id)
           ELSE
               NULL
       END    AS element
  FROM t t1
 WHERE element_id NOT LIKE 'o\_%' ESCAPE '\'
 order by question_id, element_id;

Result

   QUESTION_ID    ELEMENT_ID               ELEMENT
______________ _____________ _____________________
             1 bran          o_siera
             1 john
             2 brook         o_cody, o_daniel
             2 joseph
             3 derick        o_ashley, o_sandra
             3 james
             3 sophia