SQL Server 2014 : Convert two comma separated string into two columns

Question

I have two comma-separated string which needs to be converted into a temptable with two columns synchronized based on the index.

If the input string as below

a = 'abc,def,ghi'
b = 'aaa,bbb,ccc'

then output should be

column1 | column2
------------------
abc     | aaa
def     | bbb
ghi     | ccc

Let us say I have function fnConvertCommaSeparatedStringToColumn which takes in comma-separated string and delimiter as a parameter and returns a column with values. I use this on both strings and get two columns to verify if the count is the same on both sides. But it would be nice two have them in a single temp table. How can i do that?

score 0 · Answer 1 · answered Apr 07 '21 at 16:42

Let us say I have function which ... returns a column with values.

At that point, the basic idea is to select the column and use the row_number() function with both of your strings. Then you can JOIN the two together using the row_number() result as the matching field for the join.

score 0 · Answer 2 · answered Apr 07 '21 at 16:46

One method is a recursive CTE:

with cte as (
      select convert(varchar(max), null) as a_part, convert(varchar(max), null) as b_part,
             convert(varchar(max), 'abc,def,ghi') + ',' as a,
             convert(varchar(max), 'aaa,bbb,ccc') + ',' as b,
             0 as lev
      union all
      select convert(varchar(max), left(a, charindex(',', a) - 1)),
             convert(varchar(max), left(b, charindex(',', b) - 1)),
             stuff(a, 1, charindex(',', a), ''),
             stuff(b, 1, charindex(',', b), ''),
             lev + 1
      from cte
      where a <> '' and lev < 10
     )
select a_part, b_part
from cte
where lev > 0;

Here is a db<>fiddle.

Stu · Answer 3 · 2021-04-07T17:10:55.233

Here's something a bit sneaky you can try.

I don't have your bespoke function so have used the built-in string_split function (SQL2016+) - for quickly testing, but assuming the parameters are the same. Ideally, your bespoke function should return its own row number in which case you'd use that instead of a rownumber function.

declare @a varchar(20)='abc,def,ghi', @b varchar(20)='aaa,bbb,ccc';
    with v as (
        select a.value A,b.value B, 
          row_number() over(partition by a.value order by (select 1/0))Arn,
          row_number() over(partition by b.value order by (select 1/0))Brn
        from fnConvertCommaSeparatedStringToColumn (@a,',')a
        cross apply fnConvertCommaSeparatedStringToColumn (@b,',')b
    )
    select A,B from v
    where Arn=Brn

score 0 · Answer 4 · answered Apr 07 '21 at 17:18

I would suggest getting a (set based) function that can split a string, based on a delimiter, that returns the ordinal position as well. For example DelimitedSplit8k_LEAD. Then you can trivially split the value, and JOIN on the ordinal position:

DECLARE @a varchar(100) = 'abc,def,ghi';
DECLARE @b varchar(100) = 'aaa,bbb,ccc';

SELECT A.Item AS A,
       B.Item AS B
FROM dbo.delimitedsplit8k_lead(@a,',') A
     FULL OUTER JOIN dbo.delimitedsplit8k_lead(@a,',') B ON A.ItemNumber = B.ItemNumber;

db<>fiddle

I use a FULL OUTER JOIN and then if either column has a NULL value you know that the 2 delimited lists don't have the same number of delimited values.

SQL Server 2014 : Convert two comma separated string into two columns

4 Answers4

Linked