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

zrfyljdw 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(93)

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

mysql

来源：https://stackoverflow.com/questions/74837514/mysql-view-with-row-values-concatenated-ordered-and-grouped-in-a-column

1条答案

按热度按时间

lp0sw83n1#

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 .

赞(0）回复(0）举报 2022-12-22

我来回答

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

1条答案

相关问题

热门标签

最新问答