mv_to_string({multivalue_column_name}, [{delimeter}])
Converts multivalue fields to string fields.
MV_TO_STRING() takes this syntax.
MV_TO_STRING({multivalue_column_name}, {delimeter})- multivalue_column_name
- delimiter
Optional. The characters used to delimit values in the converted string. Maximum length is 2 characters.
Returns an alphabetically sorted, delimited string representation of a multivalue field. The default delimiter is a comma followed by a space (,).
You can’t use MV_TO_STRING() in a WHERE or GROUP BY clause. Use it in the SELECT statement only. If you run MV_TO_STRING() on single-value dimensions or on unindexed multivalue dimensions, the function returns a single value.
To enable multivalue fields, you must select the Enable indexing of multivalue fields in CRM Analytics preference in Setup. If you run MV_TO_STRING() without the preference selected, the function returns the first value in the first field only.
- From Settings, in the Quick Find box, enter
Analytics, and then select Settings from the list of Analytics options. - In Settings, select Enable indexing of multivalue fields in CRM Analytics.
This query converts the multivalue field flight_attendants to a string and returns values that contain the name “maria.”
| Flight_Attendants |
|---|
| kate, maria, mark, martin, sara |
| maria, sarah |
| kate, maria, mark, martin, sara |
| maria, sarah |
| kate, maria, mark, martin, sara |
| maria, sarah |
This query returns all values of the flight_classes field and delimits them with “;;”.
| Flight_Classes |
|---|
| business;;economy |
| business;;economy;;first |
| business;;economy |
| business;;economy |
| business;;economy |
| business;;economy;;first |
| business;;economy |
| business;;economy |
| business;;economy |
| business;;economy;;first |
The custom delimeter example returns two result values: business;;economy and business;;economy;;first. Let’s group by the flight_classes field and display the counts for each of these values. Since MV_TO_STRING() throws an error if included in a GROUP BY statement, we can nest the SELECT statement containing MV_TO_STRING() as a subquery and group the results in the outer query.
| Flight_Classes | cnt |
|---|---|
| business;;economy | 18 |
| business;;economy;;first | 6 |