السلام عليكم ورحمة الله وبركاته
----------------------------------------
اخى
شوف كده الكود ده واتبع الخطوات الموجوده فيه حيث انها شبيه للجدول اللى ارسلته . طبعا حتعدل فى الكود على حسب بيانات الجدول اللى عندك وطبعا حتعمل جدول جديد
ملاحظه :- فى الجدول الجديد مش حينفع يكون PRIMARY KEY هو STORCODE لانه حيتكرر
الكود
-- FIRST TABLE ---------------------
CREATE TABLE TMP_X( T_ID NUMBER , T_DESC VARCHAR2(10), COL1 VARCHAR2(10),COL2 VARCHAR2(10),COL3 VARCHAR2(10));
---- NEW TABLE ----------------------------
CREATE TABLE TMP_Y (T_ID NUMBER , T_DESC VARCHAR2(10) , COL VARCHAR2(10) ,COL_VALUE NUMBER(10));
--- SAMPLE DATA FOR THE FIRST TABLE
INSERT INTO TMP_X ( T_ID, T_DESC, COL1, COL2, COL3 ) VALUES (
1, 'AA', '10', '20', '30');
INSERT INTO TMP_X ( T_ID, T_DESC, COL1, COL2, COL3 ) VALUES (
2, 'BB', '40', '50', '60');
INSERT INTO TMP_X ( T_ID, T_DESC, COL1, COL2, COL3 ) VALUES (
3, 'CC', '70', '80', '90');
INSERT INTO TMP_X ( T_ID, T_DESC, COL1, COL2, COL3 ) VALUES (
4, 'DD', '55', '66', '34');
INSERT INTO TMP_X ( T_ID, T_DESC, COL1, COL2, COL3 ) VALUES (
34, 'RR', '45', '76', '87');
commit;
-- CURSOR TO INSERT DATA TO THE NEW TABLE ---------------------
BEGIN FOR REC IN ( SELECT * FROM TMP_X )
LOOP
FOR REC1 IN (
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TMP_X' AND COLUMN_NAME NOT IN ('T_ID','T_DESC')
)
LOOP
INSERT INTO TMP_Y (T_ID,T_DESC,COL) VALUES (REC.T_ID,REC.T_DESC ,REC1.COLUMN_NAME) ;
END LOOP;
END LOOP;
END;
-- UPDATE STAMENTS TO GET DATA
UPDATE TMP_Y TT
SET COL_VALUE =( SELECT COL1 FROM TMP_X T WHERE T_ID=TT.T_ID AND 'COL1' =
(SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TMP_X' AND COLUMN_NAME ='COL1')
)
WHERE COL='COL1' ;
UPDATE TMP_Y TT
SET COL_VALUE =( SELECT COL2 FROM TMP_X T WHERE T_ID=TT.T_ID AND 'COL2' =
(SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TMP_X' AND COLUMN_NAME ='COL2')
)
WHERE COL='COL2' ;
UPDATE TMP_Y TT
SET COL_VALUE =( SELECT COL3 FROM TMP_X T WHERE T_ID=TT.T_ID AND 'COL3' =
(SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TMP_X' AND COLUMN_NAME ='COL3')
)
WHERE COL='COL3' ;
COMMIT;