PostgreSQLクエリで結果をカンマ区切りのリストとして返すその他の方法

PostgreSQLクエリで結果をカンマ区切りのリストとして返す方法

CONCAT_WS() 関数を使う

CONCAT_WS() 関数は、複数の文字列を指定された区切り文字で連結します。カンマ区切りのリストを作成するには、区切り文字にカンマ(',')を指定します。

SELECT CONCAT_WS(',', column1, column2) FROM table;

このクエリは、table テーブルの column1 と column2 の値をカンマで区切って連結し、結果を返します。

ARRAY_AGG() 関数を使う

ARRAY_AGG() 関数は、複数の値を配列として返します。カンマ区切りのリストを作成するには、ORDER BY 句で配列の順序を指定し、DISTINCT キーワードを使って重複を除去します。

SELECT ARRAY_AGG(DISTINCT column1 ORDER BY column1) FROM table;

このクエリは、table テーブルの column1 の値を重複を除去して昇順に並べ替え、カンマ区切りのリストとして返します。

SELECT STRING_AGG(column1, ',') FROM table;

サブクエリを使う

サブクエリを使って、カンマ区切りのリストを作成することもできます。

SELECT column1 FROM (
  SELECT column1, ROW_NUMBER() OVER (ORDER BY column1) AS rn
  FROM table
) AS t
WHERE t.rn > 1
ORDER BY t.column1;

-- 1. CONCAT_WS() 関数を使う

SELECT CONCAT_WS(',', 'a', 'b', 'c');
-- 結果: a,b,c

-- 2. ARRAY_AGG() 関数を使う

SELECT ARRAY_AGG(DISTINCT column1 ORDER BY column1) FROM table;
-- 結果: {a, b, c}

-- 3. STRING_AGG() 関数を使う

SELECT STRING_AGG(column1, ',') FROM table;
-- 結果: a,b,c

-- 4. サブクエリを使う

SELECT column1 FROM (
  SELECT column1, ROW_NUMBER() OVER (ORDER BY column1) AS rn
  FROM table
) AS t
WHERE t.rn > 1
ORDER BY t.column1;
-- 結果: b,c

実行方法

接続したら、psqlコマンドラインツールまたはpgAdminなどのGUIツールを使ってクエリを実行できます。

結果

-- 1. CONCAT_WS() 関数を使う

a,b,c

-- 2. ARRAY_AGG() 関数を使う

{a, b, c}

-- 3. STRING_AGG() 関数を使う

a,b,c

-- 4. サブクエリを使う

b,c

FOR XML PATH() を使う

FOR XML PATH() を使って、XML形式で結果を返し、XPathを使ってカンマ区切りのリストを作成することができます。

SELECT column1
FROM table
FOR XML PATH('');

-- 結果: <row><column1>a</column1></row><row><column1>b</column1></row><row><column1>c</column1></row>

上記のクエリは、table テーブルの column1 の値をXML形式で返し、XPathを使ってカンマ区切りのリストを作成します。

PL/pgSQL を使う

PL/pgSQLを使って、独自の関数を作成することができます。

CREATE FUNCTION comma_separated_list(
  column1 text
) RETURNS text AS $$
DECLARE
  result text;
BEGIN
  SELECT column1 INTO result;
  LOOP
    SELECT column1 INTO result
    FROM table
    WHERE column1 > result
    ORDER BY column1
    LIMIT 1;
    IF FOUND THEN
      result := result || ',';
    ELSE
      EXIT;
    END IF;
  END LOOP;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT comma_separated_list(column1) FROM table;

-- 結果: a,b,c

上記のクエリは、table テーブルの column1 の値をカンマ区切りのリストとして返すPL/pgSQL関数を作成します。

外部ツールを使う

csvtool などの外部ツールを使って、結果をCSV形式で出力し、カンマ区切りのリストを作成することができます。

COPY (SELECT column1 FROM table) TO STDOUT WITH CSV HEADER;

-- 結果: a,b,c