I'm trying to achieve below result output

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:

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

OR

OR 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

Its not an ideal data model. Something like this should work except when a value that begins with 'o_%' is an element_id and not an element.

This was not tested.

select t1.question_id
    ,case when t1.element_id not like 'o_%' then t1.element_id else '' end element_id
    ,case when t2.element_id like 'o_%' then t2.element_id else '' end element
from table t1
    join table t2 on t1.question_id=t2.question_id