Zeilen zu Spalten wg. Serienbrief

Hallo zusammen,

ich möchte meine Kunden bzgl. SEPA anschreiben. Ein Kunde kann mehrere Bankverbindungen bei uns hinterlegt haben, ich möchte aber dass der Kunde nur einen Brief mit allen Bankverbindungen erhält.

Folgendes Beispiel:

postgres=# select * from bankverbindung;
  kundennr  |  kontonr   | bankleitzahl |          iban          |     bic
------------+------------+--------------+------------------------+-------------
 0000000001 | 0000589468 | 80050000     | DE48648648416846841358 | DEF84384138
 0000000001 | 4444489468 | 80050000     | DE48648648444854785458 | DEF84384138
 0000000001 | 4468894684 | 80050000     | DE48648555555554785458 | DEF84384138
 0000000021 | 4488544896 | 10000000     | DE48648564546484444412 | DEF85846848
 0000555021 | 8885547896 | 10000000     | DE41448568999456845622 | DEF85846848
(5 Zeilen)

Bei dem Kunden mit der Nr. 0000000001 hätte ich nun gerne als Ergebnis nur eine Zeile, aber dafür mit mehreren Spalten. Ich muss hier ein paar Hundert Kunden anschreiben, einige haben nur eine Bankverbindung, andere haben bis zu 10 hinterlegt.

Den FAQ-Beitrag hab ich mir bereits durchgelesen, aber dazu muss ja der Inhalt der Spalte fix sein.

Crosstab hab ich mir in der Doku mal angeschaut, aber entweder stell ich mich zu dumm, oder in der Doku passt was nicht.

Folgendes steht in der Doku (http://www.postgresql.org/docs/8.4/static/tablefunc.html):

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

Das gleiche ergibt bei mir:

postgres=# select * from ct;
 id | rowid | attribute | value
----+-------+-----------+-------
  1 | test1 | att1      | val1
  2 | test1 | att2      | val2
  3 | test1 | att3      | val3
  4 | test1 | att4      | val4
  5 | test2 | att1      | val5
  6 | test2 | att2      | val6
  7 | test2 | att3      | val7
  8 | test2 | att4      | val8
(8 Zeilen)


postgres=# SELECT *
postgres-# FROM crosstab(
postgres(#   'select rowid, attribute, value
postgres'#    from ct
postgres'#    where attribute = ''att2'' or attribute = ''att3''
postgres'#    order by 1,2')
postgres-# AS ct(row_name text, category_1 text, category_2 text, category_3 tex
t);
FEHLER:  Funktion crosstab(unknown) existiert nicht
ZEILE 2: FROM crosstab(
              ^
TIP:  Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen über
ein. Sie müssen möglicherweise ausdrückliche Typumwandlungen hinzufügen.
postgres=#

Jemand eine Idee für mich?

Warum müssen es mehrere Spalten sein? Du schickst doch dem Kunden keine DB-Tabelle, sondern einen brief der seine Konten auflistet. Reicht da nicht auch:

test=*# select * from sepa ;
 kunde |  konto
-------+----------
     1 | konto1_1
     1 | konto1_2
     1 | konto1_3
     2 | konto2_1
     3 | konto3_!
     3 | konto3_1
(6 rows)

Time: 0,181 ms
test=*# select kunde, array_agg(konto) from sepa group by kunde;
 kunde |          array_agg
-------+------------------------------
     1 | {konto1_1,konto1_2,konto1_3}
     2 | {konto2_1}
     3 | {konto3_!,konto3_1}
(3 rows)

Time: 0,276 ms
test=*# select kunde, array_to_string(array_agg(konto),', ') from sepa group by kunde;
 kunde |       array_to_string
-------+------------------------------
     1 | konto1_1, konto1_2, konto1_3
     2 | konto2_1
     3 | konto3_!, konto3_1
(3 rows)

Das eine ! ist ein Typo :wink:

Das jetzt aufzubereiten und je Kunde 1 Mail/Brief mit einer Aufstellung seiner Konten zu erzeugen überlasse ich Dir jetzt zur Übung :wink:

Warum müssen es mehrere Spalten sein? Du schickst doch dem Kunden keine DB-Tabelle, sondern einen brief der seine Konten auflistet. Reicht da nicht auch:

Hätte ich vielleicht mehr dazuschreiben sollen.
Es muss jedem Kunden auch noch die Mandatsreferenznummer mitgeteilt werden, die für jedes Konto unterschiedlich ist.

So sollte der Brief später aufgebaut werden, evtl wirds dann verständlicher:

"In folgender Aufstellung sehen sie Ihre bisherige Kontonummer(n) und Bankleitzahl(en) sowie die Daten im neuen SEPA-Format sowie Ihre Mandatsreferenznummer:
Kontonummer: 12345, BLZ: 10080000’; IBAN:DE6486468468468468, BIC:DEF48468468, Mandatsreferenznummer: M001 "

Das ganze soll dann praktisch eine Liste werden.

Wo genau ist da das Problem?

test=*# select * from sepa ;
 kunde |  konto   |            refnummer
-------+----------+----------------------------------
     1 | konto1_1 | 05fb19e1f02d73e2f532d6628dace04f
     1 | konto1_2 | eba2f87e0445a8e6c9d240fd741f0645
     1 | konto1_3 | 2791ab938fc88de5545d96ff9775edae
     2 | konto2_1 | b29d5745fcf13e59c5de811a66611431
     3 | konto3_! | fad874a31a5931d1bcb0908d9a9ea4c2
     3 | konto3_1 | f296f41c077ac9acd3e995ba9e3929d0
(6 rows)

Time: 0,199 ms
test=*# select kunde, array_to_string(array_agg(konto || ' refno : ' || refnummer ),', ') from sepa group by kunde;
 kunde |                                                                     array_to_string
-------+--------------------------------------------------------------------------------------------------------------------------------------------
     1 | konto1_1 refno : 05fb19e1f02d73e2f532d6628dace04f, konto1_2 refno : eba2f87e0445a8e6c9d240fd741f0645, konto1_3 refno : 2791ab938fc88de5545d
     2 | konto2_1 refno : b29d5745fcf13e59c5de811a66611431
     3 | konto3_! refno : fad874a31a5931d1bcb0908d9a9ea4c2, konto3_1 refno : f296f41c077ac9acd3e995ba9e3929d0
(3 rows)

Du kannst schon quase den ganzen Brief so mit einem Select erzeugen. (Die refnummer habe ich einfach mal mit md5() aus dem Konto gemacht.)

Wo genau ist da das Problem?

Die Optik. So erhalte ich einen ziemlich langen String, der im Brief an beliebigen Stellen einen Umbruch haben wird.

Den Zeilenumbruch kannst auch mit einbauen.

Oder aber Du iterierst in Deiner Anwendung über alle Kunden und holst Dir je Kunde eine Tabelle der Kontodaten. Daraus machst dann Deinen Serienbrief.