CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
Srija Myaka
Srija Myaka

Reputation: 97

I'm trying to achieve below result output

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

Upvotes: 1

Views: 74

Answers (2)

alexherm
alexherm

Reputation: 1362

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest:

select t.*, max_o
from (select t.*,
             max(case when element_id like 'o\_%' then element_id end) over (partition by question_id) as max_o
      from t
     ) t
where element_id not like 'o\_%';

Upvotes: 1

Related Questions