CODEWITHSUNDEEP
sqlpostgresql
Akhmad Ali
Akhmad Ali

Reputation: 125

How to grouping row value in some conditional postgresql

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:

enter image description here

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

enter image description here

Can Anyone Help Me?

Upvotes: 0

Views: 40

Answers (1)

S-Man
S-Man

Reputation: 23666

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

Upvotes: 1

Related Questions