ねもぷらす

ふぁいんでぃんぐねもの日記。プログラミングとか育児とか

縦のカラムを横に並べるtips（２次元配列）

PostgreSQL PL/pgSQL

安請け合いの危険さを喉元過ぎたらすぐ忘れる。
SELECT 結果を出力するだけの簡単なお仕事です、ただし出力する１レコードには複数のレコードの情報を含めて下さい。


つまり、コレを

  name  | year | price  
--------+------+--------
 guest1 | 2001 | -15000
 guest1 | 2002 | -20000
 guest1 | 2003 | -20000
 guest1 | 2004 |  10000
 guest1 | 2005 |  15000
 guest1 | 2006 |   1000
 guest2 | 2001 |      0
 guest2 | 2002 |      0
 guest2 | 2003 |      0
 guest2 | 2004 |      0
 guest2 | 2005 |     10
 guest2 | 2006 |    100
(12 rows)

このように並べて欲しい、という依頼。

  name  | y2001  | y2002  | y2003  | y2004 | y2005 | y2006 
--------+--------+--------+--------+-------+-------+-------
 guest1 | -15000 | -20000 | -20000 | 10000 | 15000 |  1000
 guest2 |      0 |      0 |      0 |     0 |    10 |   100
(2 rows)


…私の知りうる限り、上の表から下の表を１回のSQLで導きだす方法は「無い」という結論に至っているんですが…





この際 Java でプログラム組んでやろうかと思ったけど、規模がかさみそうなので PL/SQL でなんとか解決出来ないものか模索。
いろいろな事情*1より Oracle 環境が作れないので前に作った PostgreSQL環境で試してみることに。


考えた PL/pgSQL

CREATE OR REPLACE FUNCTION fnctest1() RETURNS INT AS '
DECLARE
    w_name   varchar(8);
    w_y2001  integer;
    w_y2002  integer;
    w_y2003  integer;
    w_y2004  integer;
    w_y2005  integer;
    w_y2006  integer;
    rec record;
begin

    for rec IN select * from sample order by name,year loop
        IF w_name is not null and rec.name <> w_name THEN
            -- output
            RAISE NOTICE ''% : % % % % % %'', w_name,
                w_y2001, w_y2002, w_y2003,
                w_y2004, w_y2005, w_y2006;
            INSERT INTO sample2 values (
                w_name, w_y2001, w_y2002, w_y2003, w_y2004, w_y2005, w_y2006
            );
        END IF;

        -- setting
        w_name := rec.name;
        IF    rec.year = ''2001'' THEN w_y2001 := rec.price;
        ELSIF rec.year = ''2002'' THEN w_y2002 := rec.price;
        ELSIF rec.year = ''2003'' THEN w_y2003 := rec.price;
        ELSIF rec.year = ''2004'' THEN w_y2004 := rec.price;
        ELSIF rec.year = ''2005'' THEN w_y2005 := rec.price;
        ELSIF rec.year = ''2006'' THEN w_y2006 := rec.price;
        ELSE
            -- Exception
        END IF;
    end loop;

    -- last row output
    RAISE NOTICE ''% : % % % % % %'', w_name,
                 w_y2001, w_y2002, w_y2003,
                 w_y2004, w_y2005, w_y2006;
            INSERT INTO sample2 values (
                w_name, w_y2001, w_y2002, w_y2003, w_y2004, w_y2005, w_y2006
            );

    RETURN 0;
END;
' language 'plpgsql';


結局横一列のレコードを完成させるためにバッファを作ってあげられれば解決するはず。
出力単位でユニークになるように Order 条件をかけて、キーが変われば出力、空または同じならバッファするようなプログラム。
これだと最後のデータが出力出来ないので、ループを抜けたあとでバッファの中身を出力する。


多分コレでいけるんだけど、ダイジョブかなコレ…

*1:マシンと小生の脳のスペックに致命的な容量不足