ITS_OP/SRC/MakeRoadVertexXml.SQL

SELECT X.LEVL, X.ROAD_ID AS ROAD_ID, X.X_CRDN, X.Y_CRDN,                      
       Y.X_CRDN_MIN, Y.X_CRDN_MAX, Y.Y_CRDN_MIN, Y.Y_CRDN_MAX,                
       X.ORD, Y.ORD_CNT                                                       
  FROM (SELECT ROAD_ID, DECODE(LEVL, 1, 6, 2, 5, 5) AS LEVL,                  
               X_CRDN, Y_CRDN,                                                
               ROW_NUMBER() OVER(PARTITION BY ROAD_ID, LEVL ORDER BY ORD) ORD 
          FROM TB_ROAD_VRTX                                                   
         WHERE LEVL IN (1,2)) X,                                              
       (SELECT ROAD_ID, DECODE(LEVL, 1, 6, 2, 5, 5) AS LEVL,                  
               COUNT(1) AS ORD_CNT,                                           
               MIN(X_CRDN) AS X_CRDN_MIN, MAX(X_CRDN) AS X_CRDN_MAX,          
               MIN(Y_CRDN) AS Y_CRDN_MIN, MAX(Y_CRDN) AS Y_CRDN_MAX           
          FROM TB_ROAD_VRTX                                                   
         WHERE LEVL IN (1,2)                                                  
         GROUP BY ROAD_ID, LEVL) Y                                            
 WHERE X.LEVL = Y.LEVL                                                        
   AND X.ROAD_ID = Y.ROAD_ID                                                  
 ORDER BY X.LEVL, X.ROAD_ID, X.ORD                                            




insert into TB_ROAD_VRTX
select road_id, 2, ord, x_crdn, y_crdn from TB_ROAD_VRTX t where t.levl = 1;