CODEWITHSUNDEEP
mysqlsqllaravel
Mohsin A.
Mohsin A.

Reputation: 98

Want to get multiple rows into a single row for a user using SQL

I am looking for a solution for following:

enter image description here

Where I need a SQL query that get data for each user_id with all enrolment_value merged into a single row for the same user.

I am wondering if this is really possible or not.

I have tried Group by query but I get only 1 record not sure how to merge all the records for a user espacially under enrolment_value column.

Desired output: enter image description here

Upvotes: 0

Views: 221

Answers (1)

Christian
Christian

Reputation: 831

If you're looking for a way to aggregate all enrolment_value per user_id from MySQL, you can do so using aggregate functions like JSON_ARRAYAGG() or GROUP_CONCAT().

For example, given the following data sample on the enrolments table:

id user_id enrolment_value
1 1 {"a": "value 1"}
2 2 {"a": "value 2"}
3 1 {"a": "value 3"}

When we use JSON_ARRAYAGG as follows:

SELECT
    user_id,
👉 JSON_ARRAYAGG(enrolment_value) AS enrolment_values
FROM enrolments
GROUP BY user_id;

We get:

user_id enrolment_values
1 [{"a": "value 1"},{"a": "value 3"}] 👈
2 [{"a": "value 2"}]

Thanks to the GROUP BY clause, all rows with identical user_ids will be "squashed" into one row. But because we paired the clause to JSON_ARRAYAGG, all enrolment_values of the rows that will be squashed, will be "encoded" into a JSON array.

Now, with all enrolment_values of unique user_ids merged into one as JSON array, decoding said JSON should be pretty trivial to do in Laravel.

Upvotes: 1

Related Questions