Sorry, I read too fast and missed that part.

For the stream that will go into table2, you can use unpivot, specifying ID as passthrough, Seq as PivotKey column, City1/ST1 with pivot key value 1; City2/St2 with pivot key value 2, etc. and destination columns of City/St.

That would give you a dataset in the format you are wanting. Then you could just map that to your destination.