title: Split comma separated string into rows simplification
body: <p>Note that this is for SQL Server 2014 so various built in functions are unavailable, and I don't have permission to create functions.</p>
  
  <p>My data looks like this:</p>
  
  <pre><code>advisor_rep_id  individual_url_rep_codes    split_url_rep_codes
  57444           9289                        9569
  4407            397                         7128, 9226
  52779           8613, 8614, 8616            (null)
  56732           (null)                      9193, 4423
  56713           3456                        9193, 4423
  </code></pre>
  
  <p>I am trying to convert every variation of <code>individual_url_rep_codes</code> and <code>split_url rep_codes</code> into one column (many rows) <code>urls</code>. Note that there can be more than 5 instances of both <code>individual_url_rep_codes</code> and <code>split_url rep_codes</code> per <code>advisor_rep_id</code>.</p>
  
  <p>Expected output:</p>
  
  <pre><code>advisor_rep_id  urls
  57444           9289  
  57444           9569
  4407            397
  4407            7128
  4407            9226
  52779           8613
  52779           8614
  52779           8616
  56732           9193
  56732           4423
  56713           3456
  56713           9193
  56713           4423
  </code></pre>
  
  <p>In my attempt below I am taking the first variable in <code>individual_url_rep_codes</code> then <code>union</code> with the second variable in <code>individual_url_rep_codes</code> then <code>union</code> with the first variable in <code>split_url_rep_codes</code> then <code>union</code> with the second variable in <code>split_url_rep_codes</code> and I would continue unioning to cover for all comma separated variables in the string. </p>
  
  <p>There has to be another way! Any ideas?
  FYI, I know I get a bunch of nulls in the output which will require a subquery to get rid off. </p>
  
  <pre><code>with cte as (
      SELECT advisor_rep_id, 
  CONVERT(XML,'&lt;Emails&gt;&lt;email&gt;'  + REPLACE(individual_url_rep_codes,', ', '&lt;/email&gt;&lt;email&gt;') + '&lt;/email&gt;&lt;/Emails&gt;') AS individual_urls,
  CONVERT(XML,'&lt;Emails&gt;&lt;email&gt;'  + REPLACE(split_url_rep_codes,', ', '&lt;/email&gt;&lt;email&gt;') + '&lt;/email&gt;&lt;/Emails&gt;') AS split_urls
      from table
              )
  
  SELECT advisor_rep_id,
  CASE PATINDEX('%[ ;' + CHAR(10) + ']%', LTRIM(individual_urls.value('/Emails[1]/email[1]','varchar(100)')))
  WHEN 0 THEN LTRIM(individual_urls.value('/Emails[1]/email[1]','varchar(100)'))
  ELSE SUBSTRING(LTRIM(individual_urls.value('/Emails[1]/email[1]','varchar(100)')), 1, PATINDEX('%[ ;' + CHAR(10) + ']%',LTRIM(individual_urls.value('/Emails[1]/email[1]','varchar(100)'))) - 1) end as urls
  from cte
  where individual_urls is not null
  
  union all
  
  SELECT advisor_rep_id,
  CASE PATINDEX('%[ ;' + CHAR(10) + ']%', LTRIM(individual_urls.value('/Emails[1]/email[2]','varchar(100)')))
  WHEN 0 THEN LTRIM(individual_urls.value('/Emails[1]/email[2]','varchar(100)'))
  ELSE SUBSTRING(LTRIM(individual_urls.value('/Emails[1]/email[2]','varchar(100)')), 1, PATINDEX('%[ ;' + CHAR(10) + ']%',LTRIM(individual_urls.value('/Emails[1]/email[2]','varchar(100)'))) - 1) end as urls
  from cte
  where individual_urls is not null
  
  union all
  
  SELECT advisor_rep_id,
  CASE PATINDEX('%[ ;' + CHAR(10) + ']%', LTRIM(split_urls.value('/Emails[1]/email[1]','varchar(100)')))
  WHEN 0 THEN LTRIM(split_urls.value('/Emails[1]/email[1]','varchar(100)'))
  ELSE SUBSTRING(LTRIM(split_urls.value('/Emails[1]/email[1]','varchar(100)')), 1, PATINDEX('%[ ;' + CHAR(10) + ']%',LTRIM(split_urls.value('/Emails[1]/email[1]','varchar(100)'))) - 1) end as url
  from cte
  where split_urls is not null
  
  union all
  
  SELECT advisor_rep_id,
  CASE PATINDEX('%[ ;' + CHAR(10) + ']%', LTRIM(split_urls.value('/Emails[1]/email[2]','varchar(100)')))
  WHEN 0 THEN LTRIM(split_urls.value('/Emails[1]/email[2]','varchar(100)'))
  ELSE SUBSTRING(LTRIM(split_urls.value('/Emails[1]/email[2]','varchar(100)')), 1, PATINDEX('%[ ;' + CHAR(10) + ']%',LTRIM(split_urls.value('/Emails[1]/email[2]','varchar(100)'))) - 1) end as url
  from cte
  where split_urls is not null
  </code></pre>