You Asked
Hello
I am stuck with one issue.
I have one table with several lines. I would like to consolidate those data into another table
In sum, i want to insert data from multiple lines of one table to one line several column of another table.
My table source has following columns
TableSource
K1, K2, K3, Year, Month,Value
where K1 K2 K3 Year Month are unique per line
There is up to 12 lines (but not always, some month may miss) with K1 K2 K3 YEAR MONTH as unique value
I would like to insert those rows into one table
TableDestination
K1 K2 K3 YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
It means that if Month is 1 in table source then the Value column must go into JAN column
It means that if Month is 2 in table source then the Value column must go into FEB column
Is there a way to do it with sql query?
Regards
Mickael
Please find below sample as required in previous email
Result of the sql query i am looking for should be 2 rows inserted into TBL_COLUMN, one row for uniq 1000,2016 with value for RES_M01 (january) and RES_M03 (march) only and one row for uniq 2000 2016 with value for jan RES_M01 and april (RES_M04) only
CREATE TABLE "TBL_LINE"
(
--sequence
"LINEID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
--uniq
"K1" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
"YEAR" NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE,
"MONTH" NUMBER(2,0) DEFAULT 0 NOT NULL ENABLE,
"VALUE" NUMBER(*,0),
CONSTRAINT "KLINEID" PRIMARY KEY ("LINEID"),
CONSTRAINT "FLINEID" UNIQUE ("K1","YEAR", "MONTH")
);
CREATE SEQUENCE "LINEID_SEQ" MINVALUE 100 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER NOCYCLE ;
CREATE TABLE "TBL_COLUMN"
(
--sequence
"COLUMNID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
--uniq
"K1" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
"YEAR" NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE,
"RES_M01" NUMBER(*,0),
"RES_M02" NUMBER(*,0),
"RES_M03" NUMBER(*,0),
"RES_M04" NUMBER(*,0),
"RES_M05" NUMBER(*,0),
"RES_M06" NUMBER(*,0),
"RES_M07" NUMBER(*,0),
"RES_M08" NUMBER(*,0),
"RES_M09" NUMBER(*,0),
"RES_M10" NUMBER(*,0),
"RES_M11" NUMBER(*,0),
"RES_M12" NUMBER(*,0),
CONSTRAINT "KCOLUMNID" PRIMARY KEY ("COLUMNID"),
CONSTRAINT "FCOLUMNID" UNIQUE ("K1", "YEAR")
);
CREATE SEQUENCE "COLUMNID_SEQ" MINVALUE 100 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER NOCYCLE ;
--Insert value 10 for january 2016, key 1000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 1000,2016,1,10);
--Insert value 10 for march 2016, key 1000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 1000,2016,3,10);
--Insert value 10 for january 2016, key 2000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 2000,2016,1,10);
--Insert value 10 for april 2016, key 2000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 2000,2016,4,10);
commit;
and Chris said...
You need to do the pivot! This can convert rows to columns as you want:
CREATE TABLE "TBL_LINE" (
--sequence
"LINEID" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
--uniq
"K1" NUMBER(38,0) DEFAULT 0 NOT NULL ENABLE,
"YEAR" NUMBER(4,0) DEFAULT 0 NOT NULL ENABLE,
"MONTH" NUMBER(2,0) DEFAULT 0 NOT NULL ENABLE,
"VALUE" NUMBER(*,0),
CONSTRAINT "KLINEID" PRIMARY KEY ("LINEID"),
CONSTRAINT "FLINEID" UNIQUE ("K1","YEAR", "MONTH")
);
CREATE SEQUENCE "LINEID_SEQ" MINVALUE 100 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 100 CACHE 20 NOORDER NOCYCLE ;
--Insert value 10 for january 2016, key 1000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 1000,2016,1,10);
--Insert value 10 for march 2016, key 1000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 1000,2016,3,10);
--Insert value 10 for january 2016, key 2000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 2000,2016,1,10);
--Insert value 10 for april 2016, key 2000
Insert into TBL_LINE values (LINEID_SEQ.NEXTVAL, 2000,2016,4,10);
select * from (
select k1, year, month, value from tbl_line
)
pivot (
min(value) for month in (1 as JAN, 2 as FEB, 3 as MAR) -- add other months as needed
);
K1 YEAR JAN FEB MAR
1,000 2,016 10 10
2,000 2,016 10
From here it's just a matter of inserting the results into your other table.
For a more detailed explanation of pivoting, read:
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment