1

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
Improve this question
7
  • 2
    Tough luck... Don't store data as comma separated items. It will only cause you lots of trouble. Commented Oct 16, 2018 at 15:13
  • @jarlh the fact that I cant create functions should tell that I am not the DBA.. Stuck in a bad situation and reaching out for a potential solution Commented Oct 16, 2018 at 15:15
  • Storing multiple values as a comma-separated list is a serious problem, no matter the database. Use separate tables. You gain nothing by stuffing multiple IDs in the same field - you can't read them afterwards, you can't use them to join with other tables, you can't index them which results in very bad performance Commented Oct 16, 2018 at 15:15
  • Then ask your DBA how they would unravel the nightmare they created for you to deal with. I understand you didn't create this but you have to deal with it. What are you expecting as output from your sample? Commented Oct 16, 2018 at 15:16
  • 1
    Tell your DBA/boss, he has two choices: give you permission to create functions, or have a solution with terrible performance that will be a maintenance nightmare in the future. Commented Oct 16, 2018 at 15:24

1 Answer 1

Reset to default
4

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I will try this out, than you so much!
@user8834780 Happy to help. Just a note: the Where is optional. Remove if you want to see the NULLs
Well done as always John. I was going to post my version of using this technique but thankfully saw yours first. Much cleaner than what I was coming up with.
Absolutely fantastic

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.