using tsql split row into multiple rows -


i looking write script change table 1 table 2.

table 1

accountno name other field

   1      mr t , mrs m lambert      xxx 

i need rewrite

table 2

accountno split name other field

   1                mr t lambert             xxx    1       b          mrs m lambert            xxx 

if you, take scripting language of choice , write converter aid. me looks perl or ruby fit task quite nicely.

for example, in ruby like:

require 'active_record'  activerecord::base.establish_connection('postgresql://localhost/db') sql = activerecord::base.connection  sql.begin_db_transaction  # fetch data source table sql.execute('select * source_table').each |source_row|   # source_row hash of following form:   # { 'col_name_1' => 'col_value_1', 'col_name_2' => ... }    # prepare transformed rows array result in destination table   transformed_rows = [ ]    # make transformed rows need, based on source fields   transformed_rows << {     'another_col_1' => source_row['col_name_1'],     # ...   }    transformed_rows.each |transformed_row|     # generate , execute insert statement every transformed_row     sql.execute("insert destination_table(...) values(...)")   end end  sql.commit_db_transaction 

undoubtedly can achieved in sql, in richer dialects pl/sql, text parsing (which doing quite lot here) not sql's strong side. therefore spend lot of time figuring out string operations in language not quite suited them.

hope helps!


Comments

Popular posts from this blog

apache - PHP Soap issue while content length is larger -

asynchronous - Python asyncio task got bad yield -

javascript - Complete OpenIDConnect auth when requesting via Ajax -