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

user_14282236 0
Viewed 36

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
oracle-sqldeveloper oracle
2 Answers

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
user_71100990

This may help

select * 
  (Select listagg(element_id, ',') within group (order by element_id) from tbl ti
    where ti.question_id = t.question_id
      and element_id like 'o%'
  ) element
from tbl t
where element_id not like 'o_%',
user_11189780
Related