カンマ区切りの文字列を行に変換

再帰SQL

1つのカラムにカンマ区切りで入っている複数の単語を分割し行に（縦展開）展開する。（※設計時に正規化しとけって話ですが・・）

select 名前,好きなもの from 好き嫌い;

+-----------------+-----------------------------------------+
| 名前            | 好きなもの                              |
+-----------------+-----------------------------------------+
| 山田　太郎      | りんご,バナナ,みかん,いちご             |
| 斎藤　花子      | 苺,無花果                               |
+-----------------+-----------------------------------------+

再帰共通テーブル式を使いカンマ区切りの文字列を分解

• 再帰共通テーブル式はwith recursiveから始まる。
• SUBSTRINGでカンマの前と後で最初の単語と残りの文字列に分解する。（カンマが無いと分解できないので文字列の最後にカンマを追加）
• UNION ALLの次のSELECT文で残りの文字列に文字がある間共通テーブルを参照し残りの文字列をカンマの前と後ろで分解する。

with recursive work as 
( 
   select 名前 
       ,SUBSTRING(concat(好きなもの,","),1,LOCATE(',',concat(好きなもの,","))-1) AS 最初の単語
       ,SUBSTRING(concat(好きなもの,","),LOCATE(',',concat(好きなもの,","))+1,LENGTH(concat(好きなもの,","))) AS 残りの文字列
   from 好き嫌い
   union all
   select 名前
       ,SUBSTRING(残りの文字列,1,LOCATE(',',残りの文字列)-1) AS 最初の単語
       ,SUBSTRING(残りの文字列,LOCATE(',',残りの文字列)+1,LENGTH(残りの文字列)) AS 残りの文字列
   from work
   where 残りの文字列 <> '' 
) select 名前,最初の単語 as 好物 from work
;

+-----------------+-----------+
| 名前            | 好物      |
+-----------------+-----------+
| 山田　太郎      | りんご    |
| 斎藤　花子      | 苺        |
| 山田　太郎      | バナナ    |
| 斎藤　花子      | 無花果    |
| 山田　太郎      | みかん    |
| 山田　太郎      | いちご    |
+-----------------+-----------+

テーブル参照

カンマで区切られた単語でテーブルを参照できる場合はテーブルを参照し縦展開する。

select 名前,分類名 from 好き嫌い
left join 分類名称 on
     好きなもの like concat('%',分類名,'%')
;

+-----------------+-----------+
| 名前            | 分類名    |
+-----------------+-----------+
| 山田　太郎      | りんご    |
| 山田　太郎      | バナナ    |
| 山田　太郎      | みかん    |
| 斎藤　花子      | 苺        |
| 斎藤　花子      | 無花果    |
+-----------------+-----------+