How to grouping row value in some conditional postgresql

Question

I have query like this:

SELECT user_input_id, 
question_id,
type,
question,
COALESCE(value_text, value_free_text, value_date::text, value_number::text, value) AS all_value
FROM(
SELECT 
a.id, 
a.user_input_id,
d.id as question_id, 
d.type,
d.matrix_subtype,
d.question, 
a.value_suggested_row, 
c.value as matrix_questions, 
a.value_suggested,
b.value,
a.value_text, 
a.value_free_text,
a.value_date,
a.value_number
FROM survey_user_input_line a 
LEFT JOIN survey_label b on b.id = a.value_suggested
LEFT JOIN survey_label c on c.id = a.value_suggested_row
LEFT JOIN survey_question d on d.id = a.question_id
LEFT JOIN survey_user_input e on e.id = a.user_input_id
WHERE a.survey_id = 6
ORDER BY question_id, a.user_input_id,  id, value_suggested_row) as TempTable

and this result:

I would like to grouping row where type is multiple_choice
and my wish result like this:

Can Anyone Help Me?

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Feb 10, 2021 at 8:12

S-Man · Accepted Answer · 2021-02-10 08:06:15Z

1

I would split your result into two part (with and without multiple_choice) and UNION them after grouping the multiple_choice part:

SELECT
    *
FROM (--<your query>) s
WHERE type != 'multiple_choice'

UNION

SELECT
    user_input_id,
    question_id,
    type,
    question,
    STRING_AGG(all_value, ',')
FROM (--<your query>) s
WHERE type = 'multiple_choice'
GROUP BY user_input_id, question_id, type, question

It could be more performant to put your subquery into a CTE:

WITH my_cte AS (                       
    -- <your query>
)
SELECT
    *
FROM my_cte 
WHERE type != 'multiple_choice'

UNION

SELECT
    user_input_id,
    question_id,
    type,
    question,
    STRING_AGG(all_value, ',')
FROM my_cte
WHERE type = 'multiple_choice'
GROUP BY user_input_id, question_id, type, question

answered Feb 10, 2021 at 8:06

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Akhmad Ali Over a year ago

Thank a lot for your tips @S-Man

Collectives™ on Stack Overflow

How to grouping row value in some conditional postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related