CODEWITHSUNDEEP
sqlsql-server
user8834780
user8834780

Reputation: 1670

Split comma separated string into rows simplification

Note that this is for SQL Server 2014 so various built in functions are unavailable, and I don't have permission to create functions.

My data looks like this:

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

I am trying to convert every variation of individual_url_rep_codes and split_url rep_codes into one column (many rows) urls. Note that there can be more than 5 instances of both individual_url_rep_codes and split_url rep_codes per advisor_rep_id.

Expected output:

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

In my attempt below I am taking the first variable in individual_url_rep_codes then union with the second variable in individual_url_rep_codes then union with the first variable in split_url_rep_codes then union with the second variable in split_url_rep_codes and I would continue unioning to cover for all comma separated variables in the string.

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. 

with cte as (
    SELECT advisor_rep_id, 
CONVERT(XML,'<Emails><email>'  + REPLACE(individual_url_rep_codes,', ', '</email><email>') + '</email></Emails>') AS individual_urls,
CONVERT(XML,'<Emails><email>'  + REPLACE(split_url_rep_codes,', ', '</email><email>') + '</email></Emails>') 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

Upvotes: 1

Views: 68

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

This is a simple XML Parse with a little twist... We just concatenate the two rep_code columns.

Example

Declare @YourTable Table ([advisor_rep_id] varchar(50),[individual_url_rep_codes] varchar(50),[split_url_rep_codes] varchar(50))
Insert Into @YourTable Values 
 (57444,'9289','9569')
,(4407,'397','7128, 9226')
,(52779,'8613, 8614, 8616',NULL)
,(56732,NULL,'9193, 4423')
,(56713,'3456','9193, 4423')

Select A.[advisor_rep_id]
      ,URLS = B.RetVal
 from @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(concat([individual_url_rep_codes],',',[split_url_rep_codes]),',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
 Where B.RetVal is not null

Returns

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

Upvotes: 4

Related Questions