Hallo Andreas,
habe mal ein wenig versucht, bekomme aber nur “Müll raus”.
Anbei erst mal das CREATE und INSERT Script. Paste den Text direkt hier ein. Ich vermute mal, dass das anhängen des *.txt-Files (Windows Editor) nicht sauber klappt.
Create:
CREATE TABLE INNBDCOST
(
RNUNID VARCHAR2 (255) NOT NULL,
VALUETYPE VARCHAR2 (255) NOT NULL,
SCT1VALUE NUMBER (38,5),
STARTYEAR NUMBER,
VAL00 NUMBER (38,5),
VAL01 NUMBER (38,5),
VAL02 NUMBER (38,5),
VAL03 NUMBER (38,5),
VAL04 NUMBER (38,5),
VAL05 NUMBER (38,5),
VAL06 NUMBER (38,5),
VAL07 NUMBER (38,5),
VAL08 NUMBER (38,5),
VAL09 NUMBER (38,5),
VAL10 NUMBER (38,5),
VAL11 NUMBER (38,5),
VAL12 NUMBER (38,5),
VAL13 NUMBER (38,5),
VAL14 NUMBER (38,5),
VAL15 NUMBER (38,5),
VAL16 NUMBER (38,5),
VAL17 NUMBER (38,5),
VAL18 NUMBER (38,5),
VAL19 NUMBER (38,5)
);
INSERT:
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘EXTBAYERCOST’, NULL, 1999, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘INNCOST’, NULL, 2001, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘INTCOST’, NULL, 2000, 0, 0, 0, 0, 0, 0, 10, 0, 40, 50, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘TOTALCOST’, NULL, 2002, 0, 0, 0, 0, 0, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘DEVCOST’, NULL, 2004, 0, 20, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘EXTBMSCOST’, NULL, 2000, 0, 20, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91E6FBC1257395006959FF’, ‘EXTBMSCOST’, NULL, 2001, 0, 20, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91B6FBC667395006959FF’, ‘EINNCOST’, NULL, 2004, 0, 20, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91B6FBC667395006959FF’, ‘INTCOST’, NULL, 2003, 0, 10, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
INSERT INTO INNBDCOST( RNUNID, VALUETYPE, SCT1VALUE, STARTYEAR, VAL00, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19) VALUES( ‘AC948A9C4A91B6FBC667395006959FF’, ‘INTCOST’, NULL, 2005, 0, 20, 30, 50, 60, 0, 30, 50, 70, 90, 0, 200, 100, 0, 0, 0, 0, 0, 0, 0);
Habe mal nur 10 Datensätze genommen, das muss erst mal reichen.
Habe dann mit folgenden Statement versucht:
select rnunid,
sum(case when startyear=2005 then VAL00
when startyear=2004 then VAL01
when startyear=2003 then VAL02
when startyear=2002 then VAL03
when startyear=2001 then VAL04
when startyear=2000 then VAL05
when startyear=1999 then VAL06
end) “Cost (2005)”,
sum(case when startyear=2005 then VAL01
when startyear=2004 then VAL02
when startyear=2003 then VAL03
when startyear=2002 then VAL04
when startyear=2001 then VAL05
when startyear=2000 then VAL06
when startyear=1999 then VAL07
end) “Cost (2006)”
from innbdcost
group by rnunid
;
Aber da kriege ich nicht raus, was ich will.
Ich erwarte folgende ausgabe:
RNUNID COST (2005) COST (2006) COST (2007) Cost (2008) Cost (2009)
1 20 30 40 100 100
…
und das über die gesamte Tabelle.
Mein u.a. Statement wirft mir nur genau 2 Datensätze aus. Das kann doch nciht sein, dass ich für jeden Fall jeweils ein “Case when” schreiben muss.
???
Ratlosigkeit
Danke