Oracle row to column + sort

--1. Deleting temporary tables drop table biz_bus_station_direct_0711;--2. Placing site data, etc. into a temporary tableCreate Tablebiz_bus_station_direct_0711 asSelectLs.line_no Line_no, Bb.line_name line_name, T1.line_direct line_direct, s.station_id station_id, S . Station_Name Station_Name, T.point_num point_num_now, s.location_x x_now, s.location_y y_now, T1.       Point_num Point_num_last, t1.point_x x_last, t1.point_y y_last, s.location_x-t1.point_x X_value, S.location_y-t1.point_y Y_value,0Direction0Rn fromBiz_bus_station sJoinBiz_bus_line_station ls onls.station_id = s.station_id Left JoinBiz_bus_line BB onLs.line_no=bb.line_no Left JoinBiz_bus_line_distance T ont.station_id = s.station_id andT.line_no = Ls.line_no Left JoinBiz_bus_line_distance T1 onT1.line_no = T.line_no andT1.point_num +1= T.point_num andT.line_direct = T1.line_direct;/*select ls.line_no line_no, s.station_id station_id, S.station_name station_name, T.point_num Point       _num_now, s.location_x X_now, s.location_y y_now, T1.point_num point_num_last, t1.point_x x_last, T1.point_y Y_last, s.location_x-t1.point_x x_value, s.location_y-t1.point_y y_value, 0 directi On, 0 RN from biz_bus_station s join biz_bus_line_station ls in ls.station_id = s.station_id LEFT Join Biz_bus _line_distance T on t.station_id = s.station_id and T.line_no = Ls.line_no left joins Biz_bus_line_distance T1 on T1.line_no = t.line_no and t1.point_num + 1 = t.point_num and T.line_direct = t1.line_direct;*/ --where s.station_name= ' Anhui University of Engineering-2 ' --order by Ls.line_no, T.point_num;--3. New Route site Field ALTER TABLE biz_bus_station_direct_0711 ADD station_direct CLOB; --4. Creating a related index Create index idx_bus_station_line_no  on biz_bus_station_direct_0711 (line_no,station_id);--5. Assigning a value to a line numberbegin  for  T2 Span class= "Hljs-keyword" >in  (select  T1.line_no,t1.station_id,rank () over (Partition by  replace  ( replace  (T1.station_name,,  "), , " ) order  by  t1.line_no,t1.station_id) rn from  biz_bus_station_direct_0711 t1) Loop update  biz_bus_station_direct_0711 T set  t.rn=t2.rn where  t.line_no=t2.line_no and  t.station_id=t2.station_id;      commit;   end loop; end;--6. Based on line number, row by line value, assign direction value/*cosθ= vector A. Vector b/| vector a|x| vector b|= (x1x2+y1y2)/[√ (X12+Y12) *√ (x22+y22)] The angle is less than 180 degrees, then the cosθ is greater than 0, indicating that the current calculated vehicle is in accordance with the rn=1 vehicle direction */ begin   forT2inch(Select Replace(Replace(T1.station_name,'-1 ',"'),'-2 ',"') Station_name,t1.x_value,t1.y_value,t1.rn frombiz_bus_station_direct_0711 T1wheret1.rn=1) loopUpdatebiz_bus_station_direct_0711 TSett.direction= Case  whent.x_value!=0  andt.y_value!=0  andt2.x_value!=0  andt2.y_value!=0  Then( Case  when(t2.x_value*t.x_value+t2.y_value*t.y_value)/(SQRT (T2.x_value*t2.x_value+t2.y_value*t2.y_value) *SQRT (t.x_value* T.x_value+t.y_value*t.y_value)) >0  Then 1 Else-1 End)Else 1 End    whereT.rn <= (Select Max(RN) frombiz_bus_station_direct_0711where Replace(Replace(Station_Name,'-1 ',"'),'-2 ',"') =t2.station_name) and Replace(Replace(T.station_name,'-1 ',"'),'-2 ',"') =t2.station_name;     commit;   end loop; end;--7. Assigning routes to individual lines based on site (row to column + sort)begin   forT2inch(SelectA.line_no,a.line_direct,Max(KEY) Station_direct from(SelectT.line_no,t.line_direct,wmsys. Wm_concat (T.station_name) over (PARTITION byT.line_no,t.line_directORDER  byT.line_direct,t.point_num)KEY, Row_number () over (PARTITION byT.line_no,t.line_directORDER  byT.line_direct,t.point_num) RS from(SelectT1.line_no,t1.line_direct,t1.point_num,t2.station_name fromBiz_bus_line_distance T1JoinBiz_bus_station T2 ont1.station_id=t2.station_idOrder  byT1.line_direct,t1.point_num) T) AGroup  byA.line_no,a.line_direct) loopUpdatebiz_bus_station_direct_0711 BBSSetBbs.station_direct=t2.station_directwhereBbs.line_no=t2.line_no andBbs.line_direct=t2.line_direct;     commit;   end loop; end;

Stored procedures:

 Create or replace procedure usp_bus_station_direct_update is/*-- Name: Bus station Direction synchronization-function: will be the way of the bus stations in the same direction 1, different direction set-1 --race Tai Sui in----------------12 */begin-delete intermediate table data Delete  from Biz_bus_station_direct;--The full amount of data such as bus stations into the intermediate tableInsert  intoBiz_bus_station_directSelectLs.line_no Line_no, Bb.line_name line_name, T1.line_direct line_direct, s.station_id station_id, S . Station_Name Station_Name, T.point_num point_num_now, s.location_x x_now, s.location_y y_now, T1.       Point_num Point_num_last, t1.point_x x_last, t1.point_y y_last, s.location_x-t1.point_x X_value, S.location_y-t1.point_y Y_value,0Direction0Rn"'Station_direct, Sysdate Create_time fromBiz_bus_station sJoinBiz_bus_line_station ls onls.station_id = s.station_id Left JoinBiz_bus_line BB onLs.line_no=bb.line_no Left JoinBiz_bus_line_distance T ont.station_id = s.station_id andT.line_no = Ls.line_no Left JoinBiz_bus_line_distance T1 onT1.line_no = T.line_no andT1.point_num +1= T.point_num andT.line_direct = T1.line_direct;--Assigning a value to a line numberFor T2 in ( Select T1.line_no,t1.station_id,rank () over (partition  by replace(replace( T1.station_name,'-1 ','),'-2 ',') order  by T1.line_no, T1.STATION_ID) RN  from Biz_bus_station_direct T1) Loop update biz_bus_station_direct t se T t.rn=t2.rn where t.line_no=t2.line_no  and t.station_id=t2.station_id;   end loop;--based on line number, row by line value, assign direction value/*cosθ= vector A. Vector b/| vector a|x| vector b|= (x1x2+y1y2)/[√ (X12+Y12) *√ (x22+y22)] The angle is less than 180 degrees, then the cosθ is greater than 0, indicating that the current calculated vehicle is in accordance with the rn=1 vehicle direction */ For T2 in (Select Replace(Replace(T1.station_name,'-1 ',"'),'-2 ',"') Station_name,t1.x_value,t1.y_value,t1.rn fromBiz_bus_station_direct T1wheret1.rn=1) loopUpdateBiz_bus_station_direct TSett.direction= Case  whent.x_value!=0  andt.y_value!=0  andt2.x_value!=0  andt2.y_value!=0  Then( Case  when(t2.x_value*t.x_value+t2.y_value*t.y_value)/(SQRT (T2.x_value*t2.x_value+t2.y_value*t2.y_value) *SQRT (t.x_value* T.x_value+t.y_value*t.y_value)) >0  Then 1 Else-1 End)Else 1 End    whereT.rn <= (Select Max(RN) fromBiz_bus_station_directwhere Replace(Replace(Station_Name,'-1 ',"'),'-2 ',"') =t2.station_name) and Replace(Replace(T.station_name,'-1 ',"'),'-2 ',"') =t2.station_name;   end loop;--site assigned to each route according to the siteFor T2 in (SelectA.line_no,a.line_direct,Max(KEY) Station_direct from(SelectT.line_no,t.line_direct,wmsys. Wm_concat (T.station_name) over (PARTITION byT.line_no,t.line_directORDER  byT.line_direct,t.point_num)KEY, Row_number () over (PARTITION byT.line_no,t.line_directORDER  byT.line_direct,t.point_num) RS from(SelectT1.line_no,t1.line_direct,t1.point_num,t2.station_name fromBiz_bus_line_distance T1JoinBiz_bus_station T2 ont1.station_id=t2.station_idOrder  byT1.line_direct,t1.point_num) T) AGroup  byA.line_no,a.line_direct) loopUpdateBiz_bus_station_direct BBSSetBbs.station_direct=t2.station_directwhereBbs.line_no=t2.line_no andBbs.line_direct=t2.line_direct;   end loop;   commit;--Exception handlingException when others then rollback; end usp_bus_station_direct_update;

Oracle row to column + sort