This discussion is archived
1 Reply Latest reply: Jan 21, 2013 1:10 AM by jeneesh RSS

Suggestion required from Column to ROW conversion

SachinSrivastava Newbie
Currently Being Moderated
Hello Experts,

My requirement is to converts row to Column information and sum of column in Weight kg in below manner

Data in columns are not of same nature they can keep on changing

Item Supplier Consumer Package DWP_NO DWP_ED Quality_code Pakaging Material WEIght kg
80026020 13984
90225217 21385 1 1 3 Not Applicable 22-paper .001
90218863 16578 1 1 1 Not Applicable 04-low density polyethylene (0.002+.002+.002)=.008
90218349 14507 1 1 1 Not Applicable 05-polypropylene,22-paper (.02+.05) =.07
40221108 21519 3,2,1 1 2 IKEA-CB-60 22-paper (.32+0.3+0.45)=1.07


Please find table structure


CREATE TABLE TEST_ROW_TO_COL
(
ITEM_CODE VARCHAR2(10 CHAR),
SUPPLIER_CODE VARCHAR2(10 CHAR),
"Consumer Package" NUMBER(2),
REQ_DWP_NO NUMBER(38),
REQ_DWP_ED NUMBER(38),
"Quality Code" VARCHAR2(35 CHAR),
"Packaging Material" VARCHAR2(56 CHAR),
"Weight Kg" NUMBER(7,3)
)

ans insert statement of sample data



--SQL Statement which produced this data:
-- select * from Test_row_to_col where
item_code in ('80026020','90225217','90218863','90218349','40221108')
--
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('80026020', '13984', NULL, NULL, NULL,
NULL, NULL, NULL);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90225217', '21385', 1, 1, 3,
'Not Applicable', '22-paper', 0.001);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218863', '16578', 1, 1, 1,
'Not Applicable', '04-low density polyethylene', 0.002);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218863', '16578', 1, 1, 1,
'Not Applicable', '04-low density polyethylene', 0.002);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218863', '16578', 1, 1, 1,
'Not Applicable', '04-low density polyethylene', 0.002);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218863', '16578', 1, 1, 1,
'Not Applicable', '04-low density polyethylene', 0.002);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218349', '14507', 1, 1, 3,
'Not Applicable', '05-polypropylene', 0.02);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('90218349', '14507', 1, 1, 3,
'Not Applicable', '22-paper', 0.05);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('40221108', '21519', 3, 1, 2,
'IKEA-CB-60', '22-paper', 0.32);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('40221108', '21519', 2, 1, 2,
'IKEA-CB-60', '22-paper', 0.3);
Insert into DWP.TEST_ROW_TO_COL
(ITEM_CODE, SUPPLIER_CODE, "Consumer Package", REQ_DWP_NO, REQ_DWP_ED, "Quality Code", "Packaging Material", "Weight Kg")
Values
('40221108', '21519', 1, 1, 2,
'IKEA-CB-60', '22-paper', 0.45);
COMMIT;


I am using Oracle 11g

Thanks in advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points