将多行合并为一列中具有多个行值的行 [英] Merge multiple rows into one with more than one row value in a column

问题描述

我目前正在构建一个查询以从我的数据库中检索一些数据，我需要在一行中访问一些具有公共 ID 的信息.

I'm currently building a query to retrieve some data from my db, I need to access some information with a common id in just one row.

使用此查询:

select 
        missions_answer.response_id as "response",
        crm_player."document" as "document",
        missions_question.label as "label",
        missions_answertext.body as "bill #",
        missions_answerselectmultiple.body as "product",
        missions_answerinteger.body as "answer" 
from missions_answer 
    left join missions_question on missions_answer.question_id = missions_question.id 
    left join missions_answertext on missions_answer.id = missions_answertext.answer_ptr_id 
    left join missions_answerselectmultiple on missions_answer.id = missions_answerselectmultiple.answer_ptr_id
    left join missions_answerinteger on missions_answer.id = missions_answerinteger.answer_ptr_id 
    left join missions_response on missions_answer.response_id = missions_response.id
    left join crm_player on missions_response.player_id = crm_player.id
    LEFT JOIN crm_user ON crm_player.user_id = crm_user.id
    where  missions_answer.response_id = '71788176'
    group by missions_answer.response_id, crm_player.document,missions_answertext.body,
        missions_question.label,
        missions_answerselectmultiple.body ,
        missions_answerinteger.body,
        crm_user.first_name,
        crm_user.last_name

这是我目前拥有的:

+   response    +     document    +    label    +    bill #  +    product  +  answer
-   71788176    -     79907201    -    bill #   -    26899   -             -
-   71788176    -     79907201    -    amount   -            -             -    1
-   71788176    -     79907201    -    product  -      -    {"name": "Shoes"}   -
-   71788176    -     79907201    -    price    -            -             -  25.99

这就是我要找的:

+   response    +     document    +    bill #  +    product  +  amount  +   price 
-   71788176    -     79907201    -    26899   -     shoes   -       1  -   25.99 

我一直在尝试使用 crosstab 但我仍然找不到它，在此先感谢您提供任何提示或帮助.

I've been trying to use crosstab but I'm still unable to find it, thanks in advance for any hint or help.

推荐答案

根据您当前的状态，您只需使用 FILTER 子句即可进行透视:

From your current state you simply can do the pivot using the FILTER clause:

demo:db<>fiddle

SELECT
    response,
    document,
    MAX(bill) FILTER (WHERE label = 'bill') as bill,
    MAX(answer) FILTER (WHERE label = 'amount') as amount,
    MAX(product) FILTER (WHERE label = 'product') as product,
    MAX(answer) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document

---

我不太确定，你原来的桌子是什么样子的.如果更像这样:

---

I am not quite sure, how your original table looks like. If it is more like this:

response | document | label   | value
-------: | -------: | :------ | :----
71788176 | 79907201 | bill    | 26899
71788176 | 79907201 | amount  | 1    
71788176 | 79907201 | product | shoes
71788176 | 79907201 | price   | 25.99

然后你可以像这样修改查询:

Then you can modify the query like this:

demo:db<>fiddle

SELECT
    response,
    document,
    MAX(value) FILTER (WHERE label = 'bill') as bill,
    MAX(value) FILTER (WHERE label = 'amount') as amount,
    MAX(value) FILTER (WHERE label = 'product') as product,
    MAX(value) FILTER (WHERE label = 'price') as price
FROM t
GROUP BY response, document

---

编辑:将 JSON 值添加到产品列:

---

Edit: TO added the JSON value to product column:

demo:db<>fiddle

变体 1:您可以简单地将 json 类型转换为 text 类型:

Variant 1: You could simply cast the type json into type text:

MAX(product::text) FILTER (WHERE label = 'product') as product,

变体 2:您从 name" 属性中读取值:

Variant 2: You read the value from the "name" attribute:

MAX(product ->> 'name') FILTER (WHERE label = 'product') as product,

这篇关于将多行合并为一列中具有多个行值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！