Answers.Body: <p>For every option group (that is, set of multiple values) I created two SQL views: One for splitting the varchar field correctly, and one for combining the split values with other data. All of these &quot;splitting views&quot; in the database pull data from an additional, common view named <code>match_ufid_contactid</code> (matching the Drupal user ID <code>uf_id</code> with a CiviCRM contact) and another view named <code>case_years</code> (containing the information in which year the first consultation of this case took place, which is a custom field in this system).</p>
  <p>So, let's say we have a custom field/column <code>case_result</code> of type multiple-choice (residing in the normal CiviCRM database called <code>civi_db</code> here, in a table called <code>result_table</code> here). We are splitting this column into single values with the following SQL view named <code>stringsplit_case_result</code> (residing in a second statistics database called <code>stats_db</code> here):</p>
  <pre><code>select `zz`.`case_id` AS `case_id`,
         `zz`.`contact_id` AS `contact_id`,
         `zz`.`one_split` AS `one_split`
  from
       (select cast(coalesce(nullif(substring_index(substring_index(`content_alias`.`fieldset_column`,char(1),`num`.`id`),char(1),-1),''),'0')
           as signed) AS `one_split`,
           `content_alias`.`case_id` AS `case_id`,
           `content_alias`.`contact_id` AS `contact_id`
       from
            ((select `fieldset_table`.`case_result` AS `fieldset_column`,
                     `case_contact`.`case_id` AS `case_id`,
                     `case_contact`.`contact_id` AS `contact_id`
            from (`civi_db`.`result_table` `fieldset_table`
            join `civi_db`.`civicrm_case_contact` `case_contact`
                on(`case_contact`.`case_id` = `fieldset_table`.`entity_id`))) `content_alias`
            join
                (select `a`.`h` * 10 + `b`.`t` + 1 AS `id`
                from ((select 0 AS `h` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `a`
                join (select 0 AS `t` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `b`)) `num`)
       where `num`.`id` &gt; 0) `zz`
  where `zz`.`one_split` &gt; 0
  order by `zz`.`one_split`
  </code></pre>
  <p>Then, inside <code>stats_db</code>, we create another SQL view that takes the split values from the <code>stringsplit_case_result</code> view and matches them with useful information we need for statistics (namely, the CaseID, the ContactID, the Drupal user ID, the year of first consultation, the human-readable label of this split value):</p>
  <pre><code>select `stringsplit`.`case_id` AS `case_id`,
         `stringsplit`.`contact_id` AS `contact_id`,
         `stringsplit`.`one_split` AS `one_split`,
         `option_table`.`label` AS `label`,
         `uf_table`.`uf_id` AS `uf_id`,
         years_table.first_contact AS first_contact
  from `stats_db`.`stringsplit_case_result` `stringsplit`
      join (select `optionvalue`.`label` AS `label`,
                   `optionvalue`.`value` AS `value`
           from `civi_db`.`civicrm_option_value` `optionvalue`
          where `optionvalue`.`option_group_id` = 136) `option_table`
      join `stats_db`.`match_ufid_contactid` `uf_table`
      join `civi_db`.civicrm_case `case_table`
  join `stats_db`.case_years `years_table`
  where case_table.is_deleted = 0
    and `stringsplit`.`one_split` = `option_table`.`value`
    and `uf_table`.`contact_id` = `stringsplit`.`contact_id`
    and `stringsplit`.`case_id` = case_table.id
  and stringsplit.case_id = years_table.case_id
  </code></pre>
  <p>These views rely on two additional SQL views called <code>case_years</code> and <code>match_ufid_contactid</code> (both inside <code>stats_db</code>). <code>case_years</code> supposes there is a table called <code>first_consultation</code> with a column <code>date</code> in the <code>civi_db</code> database, where <code>date</code> is in SQL date format. You can find them here:</p>
  <p><code>match_ufid_contactid</code>:</p>
  <pre><code>select `civi_db`.`civicrm_uf_match`.`uf_id` AS `uf_id`,
  `civi_db`.`civicrm_uf_match`.`contact_id` AS `contact_id` 
  from `civi_db`.`civicrm_uf_match`
  </code></pre>
  <p>and <code>case_years</code>:</p>
  <pre><code>select `case_table`.`id` AS `case_id`,
  extract(year from `first_consultation`.`date`) AS `first_contact` 
  from 
  (`civi_db`.`civicrm_case` `case_table` 
  join `civi_db`.`first_consultation` `first_consultation`) 
  where `case_table`.`id` = `first_consultation`.`entity_id`
  </code></pre>
  <p>Edit: @petednz In our system, Drupal Views doesn't recognize the CiviCRM fields as multiple value fields, which is why it doesn't allow to split the fields in Drupal.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T13:27:08.740Z
Answers.Text: Thanks for the write up and some heavy-duty SQL! Another option if it's for reporting is to set up the custom fields as two fields each: primary and secondaries, where primary is single-valued, and only primary gets used for reporting, and the secondaries can if need be just included as an "extra info" column without any processing except maybe comma separators instead of char(1). This way you can avoid both the technical issues and possible misinterpretation of multivalued data (e.g. double-counting).
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T19:23:48.987Z
Answers.Text: agree it is great to have the write up. thank you
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T19:24:42.713Z
Answers.Text: fwiw, i have a vague, and possibly incorrect memory, of being able to split the values in a Drupal View, a long time ago.
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T11:19:20.817Z
Answers.LastActivityDate: 2021-09-30T11:08:44.663Z
Answers.LastEditDate: 2021-09-30T11:08:44.663Z
Answers.UserDisplayName: emma
Answers.Body: <p>While not a SQL-specific answer, I would typically use an ETL tool like <a href="https://community.hitachivantara.com/s/article/data-integration-kettle" rel="nofollow noreferrer">Pentaho Data Integration</a> to do the data transforms SQL isn't well-suited for. PDI has both a &quot;Split fields&quot; and &quot;Split fields into rows&quot; steps - depending on whether you want your data normalized or denormalized.</p>
  <p>To deal with the control character issue, I'll generally use <code>UNHEX(01)</code> though it seems like your <code>CHAR(1)</code> serves the same purpose.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T22:10:34.273Z
Answers.Text: Finally - you're right that this isn't going to change soon.  However, with this summer's release of MariaDB 10.6 supporting the `JSON_TABLE()` function that MySQL 8.0 does, and with CiviCRM APIv4 work standardizing the metadata (sadly, there's something like 4 ways to store serialized data in a table Civi uses), we're moving toward a future where it will be simple to move all serialized data to JSON structures.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-09-28T22:10:29.943Z
Answers.LastActivityDate: 2021-09-28T22:10:29.943Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>The CiviCRM database stores multiple values as a <code>varchar</code> field (a string with separator characters). For example, the IDs 3, 9 and 15 are internally saved as <code>#3#9#15#</code> (where <code>#</code> is in fact the character <code>Ctrl+A</code> but displayed as <code>#</code> by phpMyAdmin).</p>
  <p>This is strongly recommended against (see <a href="https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows">https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows</a> ), but I suppose it will not be changed quickly.</p>
  <p>For statistical reasons, I wanted to evaluate these varchar fields containing multiple IDs. Therefore, I needed to split them. But as storing data this way is not recommended, SQL has no built-in explode/split function for strings. Also, working with the built-in SQL function <code>substring_index()</code> is no choice because the number of selected options saved in this field varies, from 0 to 15. Additionally, the maximum number is not fix, as new options may be created from time to time.</p>
  <p>So I want to share how I figured out to split these CiviCRM values with two SQL calls.</p>
  <p>I know that I already posted part of this answer in the <a href="https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows">Database Administrators Stackexchange</a> but I think this solution is highly CiviCRM specific and will probably be most useful to CiviCRM admins. So I wanted to post it here in a more detailed fashion.</p>
  
Tags: <civicase><database><sql>
Title: How to split CiviCRM multiple database values with SQL?
ThreadText: # How to split CiviCRM multiple database values with SQL?
  
  (Asked by: username_0 on 2021-09-28)
  
  The CiviCRM database stores multiple values as a `varchar` field (a string with separator characters). For example, the IDs 3, 9 and 15 are internally saved as `#3#9#15#` (where `#` is in fact the character `Ctrl+A` but displayed as `#` by phpMyAdmin).
  
  This is strongly recommended against (see <https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows> ), but I suppose it will not be changed quickly.
  
  For statistical reasons, I wanted to evaluate these varchar fields containing multiple IDs. Therefore, I needed to split them. But as storing data this way is not recommended, SQL has no built-in explode/split function for strings. Also, working with the built-in SQL function `substring_index()` is no choice because the number of selected options saved in this field varies, from 0 to 15. Additionally, the maximum number is not fix, as new options may be created from time to time.
  
  So I want to share how I figured out to split these CiviCRM values with two SQL calls.
  
  I know that I already posted part of this answer in the [Database Administrators Stackexchange](https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows) but I think this solution is highly CiviCRM specific and will probably be most useful to CiviCRM admins. So I wanted to post it here in a more detailed fashion.
  
  **Tags:** civicase, database, sql
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2021-09-28. Score: 2 | Accepted answer
  
  For every option group (that is, set of multiple values) I created two SQL views: One for splitting the varchar field correctly, and one for combining the split values with other data. All of these "splitting views" in the database pull data from an additional, common view named `match_ufid_contactid` (matching the Drupal user ID `uf_id` with a CiviCRM contact) and another view named `case_years` (containing the information in which year the first consultation of this case took place, which is a custom field in this system).
  
  So, let's say we have a custom field/column `case_result` of type multiple-choice (residing in the normal CiviCRM database called `civi_db` here, in a table called `result_table` here). We are splitting this column into single values with the following SQL view named `stringsplit_case_result` (residing in a second statistics database called `stats_db` here):
  
  ```
  select `zz`.`case_id` AS `case_id`,
         `zz`.`contact_id` AS `contact_id`,
         `zz`.`one_split` AS `one_split`
  from
       (select cast(coalesce(nullif(substring_index(substring_index(`content_alias`.`fieldset_column`,char(1),`num`.`id`),char(1),-1),''),'0')
           as signed) AS `one_split`,
           `content_alias`.`case_id` AS `case_id`,
           `content_alias`.`contact_id` AS `contact_id`
       from
            ((select `fieldset_table`.`case_result` AS `fieldset_column`,
                     `case_contact`.`case_id` AS `case_id`,
                     `case_contact`.`contact_id` AS `contact_id`
            from (`civi_db`.`result_table` `fieldset_table`
            join `civi_db`.`civicrm_case_contact` `case_contact`
                on(`case_contact`.`case_id` = `fieldset_table`.`entity_id`))) `content_alias`
            join
                (select `a`.`h` * 10 + `b`.`t` + 1 AS `id`
                from ((select 0 AS `h` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `a`
                join (select 0 AS `t` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `b`)) `num`)
       where `num`.`id` > 0) `zz`
  where `zz`.`one_split` > 0
  order by `zz`.`one_split`
  
  ```
  
  Then, inside `stats_db`, we create another SQL view that takes the split values from the `stringsplit_case_result` view and matches them with useful information we need for statistics (namely, the CaseID, the ContactID, the Drupal user ID, the year of first consultation, the human-readable label of this split value):
  
  ```
  select `stringsplit`.`case_id` AS `case_id`,
         `stringsplit`.`contact_id` AS `contact_id`,
         `stringsplit`.`one_split` AS `one_split`,
         `option_table`.`label` AS `label`,
         `uf_table`.`uf_id` AS `uf_id`,
         years_table.first_contact AS first_contact
  from `stats_db`.`stringsplit_case_result` `stringsplit`
      join (select `optionvalue`.`label` AS `label`,
                   `optionvalue`.`value` AS `value`
           from `civi_db`.`civicrm_option_value` `optionvalue`
          where `optionvalue`.`option_group_id` = 136) `option_table`
      join `stats_db`.`match_ufid_contactid` `uf_table`
      join `civi_db`.civicrm_case `case_table`
  join `stats_db`.case_years `years_table`
  where case_table.is_deleted = 0
    and `stringsplit`.`one_split` = `option_table`.`value`
    and `uf_table`.`contact_id` = `stringsplit`.`contact_id`
    and `stringsplit`.`case_id` = case_table.id
  and stringsplit.case_id = years_table.case_id
  
  ```
  
  These views rely on two additional SQL views called `case_years` and `match_ufid_contactid` (both inside `stats_db`). `case_years` supposes there is a table called `first_consultation` with a column `date` in the `civi_db` database, where `date` is in SQL date format. You can find them here:
  
  `match_ufid_contactid`:
  
  ```
  select `civi_db`.`civicrm_uf_match`.`uf_id` AS `uf_id`,
  `civi_db`.`civicrm_uf_match`.`contact_id` AS `contact_id` 
  from `civi_db`.`civicrm_uf_match`
  
  ```
  
  and `case_years`:
  
  ```
  select `case_table`.`id` AS `case_id`,
  extract(year from `first_consultation`.`date`) AS `first_contact` 
  from 
  (`civi_db`.`civicrm_case` `case_table` 
  join `civi_db`.`first_consultation` `first_consultation`) 
  where `case_table`.`id` = `first_consultation`.`entity_id`
  
  ```
  
  Edit: @username_2 In our system, Drupal Views doesn't recognize the CiviCRM fields as multiple value fields, which is why it doesn't allow to split the fields in Drupal.
  
  > **Comments:**
  > **username_1**: Thanks for the write up and some heavy-duty SQL! Another option if it's for reporting is to set up the custom fields as two fields each: primary and secondaries, where primary is single-valued, and only primary gets used for reporting, and the secondaries can if need be just included as an "extra info" column without any processing except maybe comma separators instead of char(1). This way you can avoid both the technical issues and possible misinterpretation of multivalued data (e.g. double-counting).  
  > **username_2**: agree it is great to have the write up. thank you  
  > **username_2**: fwiw, i have a vague, and possibly incorrect memory, of being able to split the values in a Drupal View, a long time ago.  
  
  ---
  
  ### Answer by username_3 on 2021-09-28. Score: 1
  
  While not a SQL-specific answer, I would typically use an ETL tool like [Pentaho Data Integration](https://community.hitachivantara.com/s/article/data-integration-kettle) to do the data transforms SQL isn't well-suited for. PDI has both a "Split fields" and "Split fields into rows" steps - depending on whether you want your data normalized or denormalized.
  
  To deal with the control character issue, I'll generally use `UNHEX(01)` though it seems like your `CHAR(1)` serves the same purpose.
  
  > **Comments:**
  > **username_3**: Finally - you're right that this isn't going to change soon.  However, with this summer's release of MariaDB 10.6 supporting the `JSON_TABLE()` function that MySQL 8.0 does, and with CiviCRM APIv4 work standardizing the metadata (sadly, there's something like 4 ways to store serialized data in a table Civi uses), we're moving toward a future where it will be simple to move all serialized data to JSON structures.  
  
  ---
  
  