【SQL】LISTAGG関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
LISTAGG(リスタッグ)関数の使用方法を紹介します。

目次

LISTAGG(リスタッグ)関数とは

LISTAGG関数を使うと、指定した列の複数行の値を連結して1行にまとめることができます。
デリミタ(区切り記号)を指定することで、カンマ区切りなどでまとめることが可能です。
戻り値は文字型です。

LISTAGG関数の書式

LISTAGG(連結したい列, 'デリミタ') WITHIN GROUP (ORDER BY 連結順の基準列)

引数	説明
連結したい列	連結にしたい列名を指定します。
デリミタ	連結にする際の区切り記号を指定します。
連結順の基準列	連結するときの順番を決める基準列を指定します。

LISTAGG関数では、指定した列の複数行の値をORDER BYで指定した基準で並び替えて、連結します。

LISTAGG関数の使用例

以下のテーブルを使って「LIATAGG関数」の動作を確認してみましょう。

NAME列の値をID列の順番で連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6;

SQLの結果

NAME列の値をDEPARTMENT_ID列の順番で連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY DEPARTMENT_ID)
FROM TEST6;

SQLの結果

COUNTRY_CDごとのNAME列の値を連結するSQLを試してみましょう。

SELECT LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6
GROUP BY COUNTRY_CD;

SQLの結果

SELECT COUNTRY_CD,LISTAGG(NAME,',') WITHIN GROUP (ORDER BY ID)
FROM TEST6
GROUP BY COUNTRY_CD;

SQLの結果

【SQL】単純CASE式・検索CASE式の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
単純CASE式・検索CASE式の使用方法を紹介します。

目次

単純CASE式・検索CASE式の使用例は以下のようなテーブルを使っています。

EMPLOYEE_ID	DEPARTMENT_ID	NAME	PHONE	SALARY	MANAGER_ID
1	10	山田	00000000000	500000	« NULL »
2	10	佐藤	00000000001	450000	1
3	10	鈴木	00000000002	450000	1
4	10	田中	00000000003	300000	1
5	10	山本	00000000004	220000	1
6	20	森	00000000005	470000	« NULL »
7	20	中村	00000000006	250000	6
8	20	松本	00000000007	220000	6
9	30	井上	00000000008	210000	10
10	30	木村	00000000009	300000	« NULL »

単純CASE式について

単純CASE式は条件分岐をするときに使います。
順番に列と条件値と比較して、列が条件値に一致する場合に対象の値を返します。
条件に一致するものが見つかると後続の条件との比較はしません。
いずれの条件値にも合致しない場合はデフォルト値を返します。(デフォルト値を指定しない場合はNULLを返します)

＜単純CASE式の書式＞

CASE 列 WHEN 条件値1 THEN 値1
        [WHEN 条件値2 THEN 値2
        ・・・・・
        WHEN 条件値n THEN 値
        ELSE デフォルト値]
END

単純CASE式のメモ
列に対してWHEN句で指定した条件に合致する場合THEN句で指定した値を返す。
WHEN句の条件に合致するものがなければELSE句のデフォルト値を返す。デフォルト値がない場合はNULLを返す。

単純CASE式の使用例

単純CASE式を使ってDEPARTMENT_IDが10の場合は経営管理部、20の場合は営業部、それ以外はその他と返すSQLを考えます。

select
  employee_id AS 従業員番号,
  case department_id when 10 then '経営管理部'
                     when 20 then '営業部'
                     else 'その他'
  end AS 部署,
  NAME AS 名前
from test5;

SQLの結果

検索CASE式について

検索CASE式も条件分岐をするときに使います。
単純CASE式とは違い条件の対象列を指定しません。
検索CASE式では条件式に「列名 演算子 値」の形式で指定ができます。
単純CASE式では対象の列に対して条件と＝(等価)のものに対して値を返していましたが、検索CASE式では等価条件以外を指定できます。

CASE WHEN 条件式1 THEN 戻り式1
    [WHEN 条件式2 THEN 戻り式2
     WHEN 条件式n THEN 戻り式n
     ELSE デフォルト値]
END

検索CASE式の使用例

salaryの値ごとにランクをつける場合を考えてみます。
salaryが250000未満はD、300000未満はC、350000未満はB、350000以上はAを返すSQLを試してみます。

select
  employee_id AS 従業員番号,
  case when salary < 250000 then 'D'
       when salary < 300000 then 'C'
	   when salary < 350000 then 'B'
	   else 'A'
  end AS 給与ランク,
  NAME AS 名前
from test5;

SQLの結果

【SQL】DECODE関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
DECODE関数の使用方法を紹介します。

目次

DECODE関数について

DECODE関数は以下のような書式で使います。
列の値が条件値1に合致する場合は値1を返し、条件値nに合致する場合は値nを返します。いずれの条件値にも合致しない場合はデフォルト値を返します。(デフォルト値を指定しない場合はNULLを返します)

書式： DECODE(列, 条件値1 , 値1[, ・・・条件値n, 値n] [, デフォルト値])

以下のテーブルを使って、実際にDECODE関数の使い方を確認してみましょう。

EMPLOYEE_ID	DEPARTMENT_ID	NAME	PHONE	SALARY	MANAGER_ID
1	10	山田	00000000000	500000	« NULL »
2	10	佐藤	00000000001	450000	1
3	10	鈴木	00000000002	450000	1
4	10	田中	00000000003	300000	1
5	10	山本	00000000004	220000	1
6	20	森	00000000005	470000	« NULL »
7	20	中村	00000000006	250000	6
8	20	松本	00000000007	220000	6
9	30	井上	00000000008	210000	10
10	30	木村	00000000009	300000	« NULL »

DECODE関数の使用例

DEPARTMENT_IDが10の場合は経営管理部、20の場合は営業部、それ以外はその他と返すSQLを考えます。

SELECT EMPLOYEE_ID,NAME,DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他') FROM TEST5;

SQLの結果

列名が「DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他')」と非常に長いので、以下のように列別名をつけてみましょう。
可読性を高めるために、インデントもつけてみます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(DEPARTMENT_ID, 10, '経営管理部',
                          20, '営業部',
                          'その他') AS 部署
FROM TEST5;

SQLの結果

DECODE関数の使用例2

MANAGER_IDに値がある場合は〇〇さんの部下、MANAGER_IDに値がない場合は部署長と返すSQLを考えてみましょう。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, NULL, '部署長',
                                        1, '山田さんの部下',
					6, '森さんの部下',
                                        10, '木村さんの部下') 
FROM TEST5;

以下のように部署長をデフォルトにする書き方もできます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, 1, '山田さんの部下',
					 6, '森さんの部下',
                                         10, '木村さんの部下',
                                          '部署長') 
FROM TEST5;