I'd like to be able to return a single cell containing a string formed from all the records in a table, ie
I want this:
At the moment the best I can do is use a variable, but this takes the same number of rows to build up:
Is there a way to return that final value in one SELECT? I tried adding a LENGTH(@um) and ORDER BY but it didn't work. I'd rather not use two SELECTS since I'd like to build this as an Access SQL Pass Through (although I know a workaround to execute multiple SELECTS in one query, I'd rather not use it as it's a bit messy)
Please help!
-Rob
Code:
mysql> SELECT OrganisationName FROM t1;
+------------------+
| OrganisationName |
+------------------+
| this |
| that |
| the other |
+------------------+
Code:
+-----------------------+
| SomeSelectFunction |
+-----------------------+
| this, that, the other |
+-----------------------+
Code:
mysql> SELECT @um:=concat_ws(", ", @um,t1.OrganisationName) FROM t1;
+-----------------------------------------------+
| @um:=concat_ws(", ", @um,t1.OrganisationName) |
+-----------------------------------------------+
| this |
| this, that |
| this, that, the other |
+-----------------------------------------------+
Please help!
-Rob