C# と VB.NET の質問掲示板

やっちと申します。よろしくお願いします。
SQLの質問です。

次のテーブル「人物」と「趣味」があります。

●テーブル「人物」
　 ＩＤ |    姓    |    名    |  生年月日  | 性別 |
　------+----------+----------+------------+------+
　 0001 | 山田     | 太郎     | 1995/01/01 | 0    |
　 0002 | 鈴木     | 一郎     | 1995/02/01 | 0    |
　 0003 | 佐々木   | 花子     | 1995/03/01 | 1    |
　

●テーブル「趣味」
　 ＩＤ | 連番 | 趣味       |
　------+------+------------+
　 0001 |    1 | 読書       |
　 0001 |    2 | 映画鑑賞   |
　 0002 |    1 | 野球       |
　 0003 |    1 | バンド     |
　 0003 |    2 | ジョギング |
　 0003 |    3 | 写真       |

これから、趣味を列にして次のような結果「台帳」を得たいのですが、どのようなＳＱＬを書いたら良いか分かりません。
どなたか教えていただけませんか？
列名の「趣味n」のnは連番と一致させる必要は無いです。
たとえば、山田さんの趣味1と趣味2が入れ替わっても問題ありません。

●テーブル「台帳」
　 ＩＤ |    姓    |    名    |  生年月日  | 性別 | 趣味1  | 趣味2      | 趣味3 |
　------+----------+----------+------------+------+--------+------------+-------+
　 0001 | 山田     | 太郎     | 1995/01/01 | 0    | 読書   | 映画鑑賞   |       |
　 0002 | 鈴木     | 一郎     | 1995/02/01 | 0    | 野球   |            |       |
　 0003 | 佐々木   | 花子     | 1995/03/01 | 1    | バンド | ジョギング | 写真  |

よろしくお願いします。

■No72017 (やっち さん) に返信

厄介なパターンですね。クロス集計とも違いますし。

列名や列数が変化する場合は、動的問い合わせの方が良いと思います。
あるいは、それを表示するシステム側で変形するとか…。


とりあえず、Oracle の場合はこのような対処方法があります。
列が分かれてるわけでは無いですけど。

--- サンプルデータの準備 --
CREATE TABLE 人物
(ＩＤ CHAR(4)  
, 姓  VARCHAR2(10) 
, 名  VARCHAR2(10) 
, 生年月日 DATE
, 性別 NUMBER(1)
, CONSTRAINT 人物PK PRIMARY KEY( ＩＤ )
);

CREATE TABLE 趣味
( ＩＤ CHAR(4)
, 連番 NUMBER(3) 
, 趣味 VARCHAR2(10) 
, CONSTRAINT 趣味PK PRIMARY KEY( ＩＤ, 連番 )
);

INSERT ALL
  INTO 人物 VALUES ( '0001', '山田'  , '太郎', DATE '1995-01-01', 0 )
  INTO 人物 VALUES ( '0002', '鈴木'  , '一郎', DATE '1995-02-01', 0 )
  INTO 人物 VALUES ( '0003', '佐々木', '花子', DATE '1995-03-01', 1 )
  INTO 趣味 VALUES ( '0001',  1, '読書' )
  INTO 趣味 VALUES ( '0001',  2, '映画鑑賞' )
  INTO 趣味 VALUES ( '0002',  1, '野球' )
  INTO 趣味 VALUES ( '0003',  1, 'バンド' )
  INTO 趣味 VALUES ( '0003',  2, 'ジョギング' )
  INTO 趣味 VALUES ( '0003',  3, '写真' )
SELECT * FROM DUAL;

--- 問い合わせ --

SELECT
  人物.ＩＤ
, 人物.姓
, 人物.名
, 人物.生年月日
, 人物.性別
, wmsys.wm_concat(趣味.趣味) 趣味リスト
FROM
  人物 LEFT JOIN 趣味 ON
  人物.ＩＤ = 趣味.ＩＤ
GROUP BY
  人物.ＩＤ
, 人物.姓
, 人物.名
, 人物.生年月日
, 人物.性別

■No72044 (やっち さん) に返信
> 動的にＳＱＬ作る方針で行こうと思います。
もしくは、趣味の上限数を想定して多めに列を組み上げるとかですかね。


> Oracleのwmsys.wm_concat関数の情報ありがとうございました。

No72021 で「SQLServerではないので、PIVOT使えない」と仰っていましたが、
そもそも、使用しておられるデータベースは何だったのでしょうか?
それによって、使える SQL 構文も全然異なってきます。


たとえば Access であれば、動的 SQL にせずとも処理できます。
使うのは、TRANSFROM 〜 PIVOT 構文です。

※Access / JET
-------------------------------------------------------------------
TRANSFORM FIRST(趣味)
SELECT ＩＤ, 姓, 名, 生年月日, 性別 FROM [
  SELECT 人物.ＩＤ, 人物.姓, 人物.名, 人物.生年月日, 人物.性別, 趣味.連番, 趣味.趣味
  FROM 人物 INNER JOIN 趣味 ON 人物.ＩＤ = 趣味.ＩＤ
]. AS WK GROUP BY ＩＤ, 姓, 名, 生年月日, 性別
ORDER BY ＩＤ
PIVOT 連番
-------------------------------------------------------------------

この場合、『趣味.連番』がそのまま列名として使われますので、
「1」「2」「3」といった列名になります。連番に抜けがあっても構いません。

INNER JOIN を LEFT JOIN にした場合は、無趣味の人もカウントされますが、
この場合、「<>」という列も追加されます。


一方、Oracle の PIVOT の場合は、動的 SQL にしないと難しいと思います。

※Oracle (11g以降)
-------------------------------------------------------------------
SELECT
  人物.ＩＤ
, 人物.姓
, 人物.名
, 人物.生年月日
, 人物.性別
, WK.*
FROM
  人物 LEFT JOIN 趣味 PIVOT (MAX(趣味) FOR 連番 IN (1, 2, 3, 4, 5)) WK
  ON 人物.ＩＤ = WK.ＩＤ
ORDER BY
  人物.ＩＤ
-------------------------------------------------------------------
IN で指定した連番で列が作られるため、たとえば趣味番号 4 を
誰も使っていなかったとしても、列「4」は生成されることになります。



※Oracle 8i Enterprise もしくは Oracle 9i Standard 以降
-------------------------------------------------------------------
SELECT WK.* FROM (
  SELECT
    人物.ＩＤ
  , 人物.姓
  , 人物.名
  , 人物.生年月日
  , 人物.性別
  , 趣味.連番
--, COUNT(趣味.連番) OVER (PARTITION BY 趣味.ＩＤ) AS 趣味数
  , LEAD( 趣味.趣味, 0 ) OVER (PARTITION BY 趣味.ＩＤ ORDER BY 趣味.連番) AS 趣味1
  , LEAD( 趣味.趣味, 1 ) OVER (PARTITION BY 趣味.ＩＤ ORDER BY 趣味.連番) AS 趣味2
  , LEAD( 趣味.趣味, 2 ) OVER (PARTITION BY 趣味.ＩＤ ORDER BY 趣味.連番) AS 趣味3
  , LEAD( 趣味.趣味, 3 ) OVER (PARTITION BY 趣味.ＩＤ ORDER BY 趣味.連番) AS 趣味4
  , LEAD( 趣味.趣味, 4 ) OVER (PARTITION BY 趣味.ＩＤ ORDER BY 趣味.連番) AS 趣味5
  FROM
    人物 LEFT JOIN 趣味 ON 人物.ＩＤ = 趣味.ＩＤ
) WK WHERE NVL(WK.連番, 1) = 1
ORDER BY WK.ＩＤ
-------------------------------------------------------------------
この手法では、抜けのあった趣味番号は前倒しに配置されます。
たとえば、田中さんの趣味番号が「1, 2, 5」となっていて 3, 4 が無い場合、
「趣味3」列に 連番5 の趣味が配置されます。


※Oracle
-------------------------------------------------------------------
SELECT
  人物.ＩＤ
, 人物.姓
, 人物.名
, 人物.生年月日
, 人物.性別
, MAX(DECODE(趣味.連番, 1, 趣味.趣味)) AS 趣味1
, MAX(DECODE(趣味.連番, 2, 趣味.趣味)) AS 趣味2
, MAX(DECODE(趣味.連番, 3, 趣味.趣味)) AS 趣味3
, MAX(DECODE(趣味.連番, 4, 趣味.趣味)) AS 趣味4
, MAX(DECODE(趣味.連番, 5, 趣味.趣味)) AS 趣味5
FROM
  人物 LEFT JOIN 趣味 ON
  人物.ＩＤ = 趣味.ＩＤ
GROUP BY
  人物.ＩＤ
, 人物.姓
, 人物.名
, 人物.生年月日
, 人物.性別
ORDER BY
  人物.ＩＤ
-------------------------------------------------------------------
こちらは PIVOT 版と同様、抜けのあった趣味番号の列も作られます。
田中さんの趣味番号が「1, 2, 5」となっていて 3, 4 が無い場合、
「趣味3」「趣味4」列が空になり、「趣味5」に趣味の名前が記載されます。