-- Split a table string to generate multiple rows of data
-- Create and test create table tablea (col1 varchar (10), col2 varchar (100) insert into tableaselect 'abc', '123' Union allselect 'bcd ', '200' -- to generate this/* col1 col2abc 111abc 222abc 333bcd 222bcd 222,333 */--------------------- Test method: Select top 333 id = identity (INT, 4000) into # temp from syscolumns, syscolumns B -- what does this mean? Select. col1, col2 = substring (. col2, B. ID, charindex (',',. col2 + ',', B. ID)-B. ID) from tablea A, # temp bwhere substring (',' +. col2, B. ID, 1) = ', '-- this method is good for order by col1 -- test result/* col1 col2abc 111abc 222abc 333bcd 222bcd 333 */-- delete test environment drop table # tempdrop table tablea2. -- create test environment/* how to split into fs nl gz allocation mode 20 1000 allocation mode 40 1000 allocation mode 60 1000 allocation mode 70 2000 allocation mode 40 2000 other 30 800 */create table testa (FS varchar (50 ), NL varchar (100), gz int) insert into testaselect 'allocation method ', '2014/1/60', 20/40 Union allselect 'allocation method', '2014/1/40 ', 2000 Union allselect 'others', '30', 800 -- Test -- create a virtual table (number of characters used for calculation) Select top 8000 identity (INT) TID into # tempfrom sysobjects A, sysobjects B select FS, NL oldnl, GZ, substring (NL + '/', tid, charindex ('/', NL + '/') -1) newnlfrom Testa, # tempwhere substring ('/' + NL, tid, 1) = '/' -- display result/* FS oldnl GZ newnl allocation mode 20/40/60 1000 20 allocation mode 20/40/60 1000 40 allocation mode 20/40/60 1000 60 allocation mode 60/70/40 2000 60 allocation mode 60/70/40 2000 70 allocation mode 60/70/40 2000 40 other 30 800 30 */-- delete test environment drop table # tempdrop table testac
