mySQL und Kreuztabellen

["Harald Krause" <h.krause_(at)_calix.de>]

Hallo,

ich habe ein Tabelle KDUms

Jahr	Monat	KumUm
---------------------
1997    m01       199
1997    m03        49
1997    m12       128
1998    m05        59
1999    m01        10
1999    m02       129
2000    m04       499
2000    m06        12
2000    m10       102

Daraus möchte ich eine (temporäre) Tabelle in dieser Form erzeugen:

Jahr    m01    m02    m03    m04    m05    m06    m07    m08    m09    m10
m11    m12
----------------------------------------------------------------------------
------------
1997    199            49
128
1998                                 59
1999     10    129
2000                         499            12                         102

Früher gab es in Paradox eine Funktion "Kreuztabelle erzeugen", gibt es mit
mySQL eine
elegante Möglichkeit, diese Aufgabe lösen? In der Doku habe ich keine
Hinweise
gefunden.


Ich würde es mit meinem derzeitigen Kenntnisstand so lösen:

Schritt 1:
----------
create TEMPORARY table KdUmJahr1 (
  Jahr   INT(4)        UNSIGNED ZEROFILL   DEFAULT '0000' NOT NULL,
  m01    DOUBLE(16,2)                      DEFAULT '0.00',
  m02    DOUBLE(16,2)                      DEFAULT '0.00',
   .
   .
  m12    DOUBLE(16,2)                      DEFAULT '0.00')


Schritt 2:
----------
Insert INTO KumUmJahr
  select Jahr, KumUm, 0 as m02, 0 as m03, 0 as m04, 0 as m05, 0 as m06,
    0 as m07, 0 as m08, 0 as m09, 0 as m10, 0 as m11, 0 as m12 from KDUms
where Monat='m01';
Insert INTO KumUmJahr
  select Jahr, 0 as m01, KumUm, 0 as m03, 0 as m04, 0 as m05, 0 as m06,
    0 as m07, 0 as m08, 0 as m09, 0 as m10, 0 as m11, 0 as m12 from KDUms
where Monat='m02';
  .
  .
  .
Insert INTO KumUmJahr
  select Jahr, 0 as m01, 0 as m02, 0 as m03, 0 as m04, 0 as m05, 0 as m06,
    0 as m07, 0 as m08, 0 as m09, 0 as m10, 0 as m11, KumUm from KDUms where
Monat='m12';

Schritt 3:
----------
Create TEMPORARY Table KumUmJahr
select Jahr,
  sum(m01) as m01,
  sum(m02) as m02,
  sum(m03) as m03,
  .
  .
  .
  sum(m12) as m12
from KumUmJahr
group by Jahr


Muß ich mir auch in Zukunft die Finger wundtippen?? :-(
Oder geht das einfacher :-)

Mit freundlichen Grüßen
Harald

mailto:h.krause_(at)_calix.de


---
*** Weitere Infos zur Mailingliste und MySQL unter http://www.4t2.com/mysql