GROUP_CONCAT function in Dataset View vs MySQL Dataflow

ARosser · 2024-06-03T18:58:24+00:00

There was an error rendering this rich post.

I have created a MySQL Dataflow where I was able to successfully use the GROUP_CONCAT function. It is preferred that I use a Dataset View in this circumstance since the underlying data is coming from AppDB and therefore updates every 15 minutes if there is new data. I have copied and pasted the exact query that is successfully running for the MySQL Dataflow. I have updated the dataset names to the aliases that the View uses. But I am getting an error. Of course the errors in SQL Editor for views are completely useless. I assume it is due to the GROUP_CONCAT function. Is it not supported for views? Below is the query I have successfully running for the MySQL Dataflow

SELECT
RECORDNO,
Location ID (ARDetail),
Department ID (ARDetail),
Customer ID (ARRecord),
Invoice Number,
GROUP_CONCAT(
CONCAT(date,' - ',IFNULL(Name,user),': ',postBody)
ORDER BY DATE(date) DESC
SEPARATOR '\r\n') AS Notes,
MAX(date) AS Most Recent
FROM collection_notes_dataset
LEFT JOIN users
ON collection_notes_dataset.user = users.User ID
GROUP BY
RECORDNO,
Location ID (ARDetail),
Department ID (ARDetail),
Customer ID (ARRecord),
Invoice Number

Find more posts tagged with

SQL

Accepted answers

Sean_Tully

The "Add Column" group by option for combining strings uses the comma as a separator, but the "Add Formula" option allows you to use the group_concat formula you've already written.

All comments

Sean_Tully

AFAIK dataset views do not have this functionality.

I do believe you can do this in Magic ETL using a Group By formula, which might be fast enough for your needs depending on the size of your inputs.

ARosser

Unfortunately the only option for a separator is a comma. The test I am separating already has commas and therefore it makes it less legible. Using the MySQL Dataflow I am able to use \r\n which acts as a line break. Thanks!

Sean_Tully

The "Add Column" group by option for combining strings uses the comma as a separator, but the "Add Formula" option allows you to use the group_concat formula you've already written.