{"id":94,"date":"2009-10-28T16:01:02","date_gmt":"2009-10-28T15:01:02","guid":{"rendered":"http:\/\/blog.rabihtawil.com\/?p=94"},"modified":"2009-10-28T16:01:02","modified_gmt":"2009-10-28T15:01:02","slug":"single-field-to-multiple-rows","status":"publish","type":"post","link":"http:\/\/www.raytawil.com\/?p=94","title":{"rendered":"Single Field to Multiple Rows"},"content":{"rendered":"<div class=\"snap_preview\">\n<p>You want your field from all in one field<\/p>\n<p>(No column name)<br \/>\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013<br \/>\nMike,Joe,Jerry,Ben<\/p>\n<p>to become separate rows like below?<\/p>\n<p>NAME<br \/>\n\u2014\u2014\u2013<br \/>\nMike<br \/>\nJoe<br \/>\nJerry<br \/>\nBen<\/p>\n<p>then use script below..<\/p>\n<pre>declare @toRowXML xml\nSET @toRowXML = '&lt;b&gt;&lt;x&gt;'+replace('Mike,Joe,Jerry,Ben',',','&lt;\/x&gt;&lt;x&gt;')+'&lt;\/x&gt;&lt;\/b&gt;'\nSELECT @toRowXML\nSELECT rtrim(ltrim(toRow.value('.','nvarchar(100)'))) as NAME\n from @toRowXML.nodes('\/b\/x') as t(toRow)<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>You want your field from all in one field (No column name) \u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013 Mike,Joe,Jerry,Ben to become separate rows like below? NAME \u2014\u2014\u2013 Mike Joe Jerry Ben then use script below.. declare @toRowXML xml SET @toRowXML = &#8216;&lt;b&gt;&lt;x&gt;&#8217;+replace(&#8216;Mike,Joe,Jerry,Ben&#8217;,&#8217;,&#8217;,'&lt;\/x&gt;&lt;x&gt;&#8217;)+'&lt;\/x&gt;&lt;\/b&gt;&#8217; SELECT @toRowXML SELECT rtrim(ltrim(toRow.value(&#8216;.&#8217;,&#8217;nvarchar(100)&#8217;))) as NAME from @toRowXML.nodes(&#8216;\/b\/x&#8217;) as t(toRow)<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[110],"tags":[],"_links":{"self":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts\/94"}],"collection":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=94"}],"version-history":[{"count":0,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts\/94\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=94"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=94"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}