データベースのテキスト列を改行で分割・結合する方法（Power Query & SQL Server）

データベースとExcelを連携させて利用していると、図のようにユーザーが入力した改行入りのデータを分割したり、逆に複数行のデータを改行で区切ってまとめて一つのセルに出力したいケースが出てきます。

改行区切りのテキストデータはデータの構造をシンプルにしつつ複数のデータを扱う場合に有効です。

また、複数行のテキストを改行区切りで１つにまとめのは、備考情報の集約などにも活用できます。

この記事では、そのような場合に役立つテクニックとして、Power Query と SQL Server それぞれにおける改行区切りのテキスト列の分割・結合方法を解説します。

これによりデータの分割・結合が自在にできるようになって、データ活用の幅が広がりますので、是非参考にしてみて下さい。

SQL Server での改行区切りテキストの分割

SQL Server では、STRING_SPLIT 関数を使って、改行区切りのテキストを複数行に分割できます。

サンプルクエリ

SELECT Category,
       ISNULL(s.value, '') AS Item
FROM TableA
OUTER APPLY STRING_SPLIT(ISNULL(Item, ''), CHAR(10)) AS s;

解説

STRING_SPLIT(Item, CHAR(10)) を使用することで、列 Item の改行（`CHAR(10)）で区切られた値を分割できます。
OUTER APPLY を使うことで、元の Category の値を保持しつつ、分割後のデータを展開できます。
ISNULL(Item, '') を指定することで、NULL の値がある場合も安全に処理できます。
ISNULL(s.value, '') を指定することで、空データの場合もエラーなく処理できます。

SQL Server での改行区切りテキストの結合

逆に、複数の行データを改行区切りの一つのテキストに結合する場合は、STRING_AGG 関数を使用します。

サンプルクエリ

SELECT
    Category,
    STRING_AGG(Item, CHAR(10)) AS Items
FROM TableA
GROUP BY Category;

解説

STRING_AGG(Item, CHAR(10)) を使用することで、Item 列のデータを CHAR(10)（改行）で結合できます。
GROUP BY Category によって、カテゴリごとにデータを集約できます。

Power Query での改行区切りテキストの分割

Power Query では、GUI 操作で簡単に改行区切りのデータを分割できます。

手順

データの取得 から対象のデータを Power Query に読み込む。
分割したい列を選択。
列の分割 → 区切り記号による分割 を選択。
区切り記号として カスタム を選ぶ
分割の文字は改行とするため、「特殊文字を使用して分割」にチェック
「特殊文字を挿入」のプルダウンから改行を選んで区切り記号欄に入力
行 で分割するオプションを選択。
OK をクリックすると、改行区切りのデータが複数行に展開される。

Power Query での改行区切りテキストの結合

Power Query の GUI では、グループ化や文字列の結合が難しいため、M 言語を使用して処理を行います。

サンプル M コード

let
    ソース = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    グループ化 = Table.Group(ソース, {"Category"}, {{"結合データ", each Text.Combine([Item], "#(lf)"), type text}})

    in
    グループ化

解説

Table.Group を使って、Category 列ごとにデータをグループ化します。
Text.Combine([Item], "#(lf)") を使って、Item 列のデータを改行（#(lf)）で結合します。
type text を指定することで、適切なデータ型を保持します。

まとめ

この記事では改行区切りのテキスト列を分解したり、逆に複数行のテキストデータを１つにまとめてしまう方法を、Power Query と SQL Server それぞれについて紹介しました。

小規模な単位でのデータ管理では厳格なデータ構造より、こういった簡易な方法でのデータを管理の方が効率的でタイムリーな運用が可能です。

是非、ご自分の業務データ管理に活かしてみて下さい。