SQLでカンマ区切りを扱うサンプルコード集 formatやCSV、group_concatの例

SQLでカンマ区切りを扱う方法について、サンプルコード付きでまとめてます。

1 SQLでカンマ区切りを扱う方法サンプルコード集
2 まとめ

SQLでカンマ区切りを扱う方法サンプルコード集

数値を3桁毎にカンマ区切り整形するにはformat関数

桁の大きな数値を読みやすく3桁毎にカンマを入れて整形するには、format関数が便利です。

第一引数に整形対象の数値、第二引数に小数点位置を指定します。以下は、MySQLで実行した例です。

mysql> select format(111111111,0);
+---------------------+
| format(111111111,0) |
+---------------------+
| 111,111,111         |
+---------------------+
1 row in set (0.00 sec)

Oracleなどformat関数が用意されていないデータベースエンジンもあります。第一引数に整形元の値を、第二引数にフォーマット用の文字列を指定するんですね。

なお、指定したフォーマット文字列で桁あふれが起こると結果が”###…”になってしまうなど、注意点もあります。

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount"
     FROM DUAL;

Amount
--------------
  $10,000.00-

参考）Oracle公式：TO_CHAR(数値)

selectした結果をカンマ区切りのCSV形式で出力する方法

select into でファイルにクエリ結果を出力する場合、fields teminated byでカラム間の区切り文字をカンマに指定することで、CSV形式での出力が可能になります。以下は、MySQLでの例です。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

CSV出力の方法はデータベースエンジンごとに異なり、標準の方法は用意されていません。例えば、Oracleの場合はカラム間を「||」で接続し、間にカンマを挿入することで、CSVファイルとしてクエリ結果を出力することができます。

SQL Serverだと、CLIコマンドのsqlcmdにフィールドセパレータとして,を区切ることでCSV形式での出力が可能です。

sqlcmd -S localhost -E -i test.sql -v param='9999' -b -s, -W -o test.csv

参考）SQLServerのデータをCSVに出力するバッチファイルを作ろう – Qiita

postgreSQLの場合も、CLIコマンドのpsqlにフィールドセパレータとしてカンマを指定することで可能です。

psql データベース名 -c "SQL文" -A -F, > CSVファイル名

参考）PostgreSQLのCSV出力(Export)方法 – Qiita

select結果の行をカンマ区切りで出力する（group_concat）

selectの抽出結果を通常の縦方向ではなく、横方向にカンマ区切りで出力するにはgroup_concatを使用します。以下は、MySQLでの例です。

mysql> select group_concat(dept_name) from departments;
+-------------------------------------------------------------------------------------------------------------+
| group_concat(dept_name)                                                                                     |
+-------------------------------------------------------------------------------------------------------------+
| Customer Service,Development,Finance,Human Resources,Marketing,Production,Quality Management,Research,Sales |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

group_concatはMySQL独自の関数です。データベースエンジンによって、別の関数や機能を組み合わせて実現可能です。

例えば、OracleではLISTAGGという関数が用意されています。

SELECT LISTAGG(last_name, '; ')
         WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
       MIN(hire_date) "Earliest"
  FROM employees
  WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

Oracle公式：LISTAGG

SQL Serverでは、FOR XML PATH(”)を使って実現できます。

参考）[SQL Server] 縦に並んだデータを横にカンマ区切りの列データで取得する方法 │ Web備忘録

postgreSQLでは、array_agg()などの配列操作関数を組み合わせて、group_concatと同等の機能が実現できます。

参考）PostgreSQL で MySQL の GROUP_CONCAT() 関数を実現する|Everything you do is practice