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
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 )