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

- : Name of the multivalue field to be converted to a string.

• 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.

note: 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.

1. From Settings, in the Quick Find box, enter Analytics, and then select Settings from the list of Analytics options.
2. In Settings, select Enable indexing of multivalue fields in CRM Analytics.

Simple Example

This query converts the multivalue field flight_attendants to a string and returns values that contain the name “maria.”

SELECT MV_TO_STRING(flight_attendants) as Flight_Attendants
FROM "FlightsWithNullDim"
WHERE "flight_attendants" IN ('maria')

Example with Custom Delimeter

This query returns all values of the flight_classes field and delimits them with “;;”.

SELECT MV_TO_STRING(flight_classes, ';;') as Flight_Classes
FROM "FlightsWithNullDim"
LIMIT 10;

Example with Grouping

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.

SELECT Flight_Classes, count() as cnt
FROM (
    SELECT MV_TO_STRING(flight_classes, ';;') as Flight_Classes
    FROM "FlightsWithNullDim"
)
GROUP BY Flight_Classes;