我正在尝试实现以下结果输出 [英] I'm trying to achieve below result output

问题描述

我们可以通过使用 PIVOT 或其他东西来获得以下所需的表格吗?我正在尝试将下表转换为所需的输出，如下所示.

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.

数据集:

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

预期结果:

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 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   

推荐答案

它不是理想的数据模型.除非以o_%"开头的值是 element_id 而不是元素，否则类似这样的事情应该可以工作.

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.

这没有经过测试.

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

这篇关于我正在尝试实现以下结果输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！