[pgsql-jp: 30912] Re: レコード内容から列名を取得・生成？するSQL

[斎見 浩平]

サイミです。

そっか、外部結合を使って

SELECT m.name AS name,
       j1.yuubin1 AS yuubin1, j1.text1 AS text1,
       j2.yuubin2 AS yuubin2, j2.text2 AS text2,
       j3.yuubin3 AS yuubin3, j3.text3 AS text3
FROM   master m
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin1,
           fk_table.text      AS text1
    FROM fk_table, location
    WHERE fk_table.number = 1
      AND fk_table.location = location.id) AS j1
  ON m.id = j1.master
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin2,
           fk_table.text      AS text2
    FROM fk_table, location
    WHERE fk_table.number = 2
      AND fk_table.location = location.id) AS j2
  ON m.id = j2.master
LEFT OUTER JOIN (
    SELECT fk_table.fk_master AS master,
           location.yuubin    AS yuubin3,
           fk_table.text      AS text3
    FROM fk_table, location
    WHERE fk_table.number = 3
      AND fk_table.location = location.id) AS j3
  ON m.id = j3.master
ORDER BY m.name;

でいいのかな？

-- 
斎見 浩平 <saimi_at_qs ＠ ybb.ne.jp>