| ■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」に趣味の名前が記載されます。 |