MySQL view with row values concatenated, ordered and grouped in a column-CodePudding

I have a table of item reviews that looks like this:

**ReviewID**  **ReviewerID**  **ReviewText**   **Category**  **ItemID**  **date_time**

 01           1212            good product      Mobile     1           23/Apr/2022
 02           1233            will buy again    fruit      2           24/Apr/2022
 03           1245            not recommended   Mobile     1           25/Apr/2022
 04           1236            not ripe          fruit      2           22/Apr/2022

I want to create a view that selects ReviewID, ReviewText and a new column called AllReviews that has all reviews of the same category and ItemID concatenated and ordered by date_time.

The view should give results like this for the above table:

**ReviewID**   **ReviewText**   **AllReviews**

  01           good product     25/Apr/2022  1  not recommended
                                23/Apr/2022  1  good product

  02           will buy again   24/Apr/2022  2  will buy again
                                22/Apr/2022  2  not ripe

  03           not recomended   25/Apr/2022  1  not recommended
                                23/Apr/2022  1  good product
 
  04           not ripe         24/Apr/2022  2  will buy again
                                22/Apr/2022  2  not ripe

In snowflake I would use Listagg for the AllReviews column, How can I get the same result for MySQL.

I would appreciate some help

CodePudding user response：

In MySQL you can use GROUP_CONCAT as the following:

CREATE VIEW my_view AS
(
  SELECT T.ReviewID, T.ReviewText, D.AllReviews
  FROM table_name T JOIN
  (
    SELECT Category, ItemID,
      GROUP_CONCAT(CONCAT(date_time, ' ', ItemID, ' ', ReviewText) ORDER BY date_time DESC SEPARATOR '\n') AllReviews
    FROM table_name
    GROUP BY Category, ItemID
  ) D
  ON T.Category = D.Category AND
     T.ItemID = D.ItemID
)

See demo.