Open-source tool: Practical experience in converting large quantities of SQL code syntax : 'PIVOT' function rewrite (Case 1)

Background : In migration projects involving different databases, incompatibility of SQL syntax is often encountered. Question : If there is a large amount of code that needs to be rewritten, manual processing would be time-consuming and prone to errors. Is it possible to achieve automatic conversion of code syntax in large quantities through tools? Solution : The open-source tool ZGLanguage can be utilized to perform automated conversion of SQL code in large batches. For example： Suppose SQL PIVOT function is as follows : SELECT * FROM ( select country , state , yr , qtr , sales , cogs from table111 ) PIVOT ( SUM ( sales ) AS ss1 , SUM ( cogs ) AS sc FOR qtr IN ( 'Q1' AS Quarter1 , 'Q2' AS Quarter2 , 'Q3' AS Quarter3 , 'Q4' AS Quarter4 ) ) tmp ; Using the ZGLanguage conversion rule, execute the conversion to obtain the result : SELECT * FROM ( select ### , ### , ### SUM ( case when qtr = 'Q1' then sales else null end ) AS Quarter1_ss1 , SUM ( case when qtr = 'Q2' then sales else null end ) AS Quarter2_ss1 , SUM ( case when qtr = 'Q3' then sales else null end ) AS Quarter3_ss1 , SUM ( case when qtr = 'Q4' then sales else null end ) AS Quarter4_ss1 , SUM ( case when qtr = 'Q1' then cogs else null end ) AS Quarter1_sc , SUM ( case when qtr = 'Q2' then cogs else null end ) AS Quarter2_sc , SUM ( case when qtr = 'Q3' then cogs else null end ) AS Quarter3_sc , SUM ( case when qtr = 'Q4' then cogs else null end ) AS Quarter4_sc from ( select country , state , yr , qtr , sales , cogs from table111 ) where qtr IN ( 'Q1' , 'Q2' , 'Q3' , 'Q4' ) group by ### , ### , ### ) tmp ; The conversion rule is as follows : __DEF_FUZZY__ Y __DEF_DEBUG__ N __DEF_CASE_SENSITIVE__ N __DEF_LINE_COMMENT__ -- __DEF_LINES_COMMENT__ /* */ __DEF_STR__ __IF_KW__ <1,100> [1,1]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz [0,100]ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789_ [NO] XXX __DEF_PATH__ __FROM_PIVOT_1_1__ 1 : frm @ %__IF_KW__ | from : tab @ | __TABLE_NAME__ : ssl @