المساعدة لتحويل جدول

السلام عليكم ورحمة الله وبركاته
----------------------------------------
اخى
شوف كده الكود ده واتبع الخطوات الموجوده فيه حيث انها شبيه للجدول اللى ارسلته . طبعا حتعدل فى الكود على حسب بيانات الجدول اللى عندك وطبعا حتعمل جدول جديد
ملاحظه :- فى الجدول الجديد مش حينفع يكون 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;