CODEWITHSUNDEEP
sqlsql-server
shdavis701
shdavis701

Reputation: 1

How To Split Separate Strings in 2 Different Columns in SQL Server

I have 2 columns of pipe delimited data that I need to break out into rows but the columns must stay together. Here's what my data looks like:

Plan Name:  ABC|DEF|GHI|JKL
Plan Type:  HMO|POS|HMO|PPO

I need to end up with 4 rows that look like this:

1 - ABC HMO
2 - DEF POS
3 - GHI HMO
4 - JKL PPO

I know how to separate each column individually using the STUFF function but how do I keep the first value from column 1 with the first value from column 2, etc? Don't know where to start. Appreciate any help!

p.s. - I am not on SQL Server 2016 so can't use STRING_SPLIT

Upvotes: 0

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

One method is a recursive CTE:

with t as (
      select *
      from (values ('ABC|DEF|GHI|JKL', 'HMO|POS|HMO|PPO')) v(plannames, plantypes)
      ),
      cte as (
       select convert(varchar(max), left(plannames, charindex('|', plannames + '|') - 1)) as planname,
              convert(varchar(max), left(plantypes, charindex('|', plantypes + '|') - 1)) as plantype,
              convert(varchar(max), stuff(plannames, 1, charindex('|', plannames + '|'), '')) as planname_rest,
              convert(varchar(max), stuff(plantypes, 1, charindex('|', plantypes + '|'), '')) as plantype_rest,
              1 as lev
       from t
       union all
       select convert(varchar(max), left(planname_rest, charindex('|', planname_rest + '|') - 1)) as planname,
              convert(varchar(max), left(plantype_rest, charindex('|', plantype_rest + '|') - 1)) as plantype,
              convert(varchar(max), stuff(planname_rest, 1, charindex('|', planname_rest + '|'), '')) as planname_rest,
              convert(varchar(max), stuff(plantype_rest, 1, charindex('|', plantype_rest + '|'), '')) as plantype_rest,
              lev + 1
       from cte
       where planname_rest <> ''
       )
select *
from cte;

Here is a db<>fiddle.

Upvotes: 1

Thom A
Thom A

Reputation: 95561

Using delimitedsplit8k_lead you could do:

SELECT CONVERT(varchar(3), itemnumber) + ' - ' + PN.item + ' ' + PT.item
FROM YourTable YT
     CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanName,'|') PN
     CROSS APPLY dbo.delimitedsplit8k_lead(YT.PlanType,'|') PT
WHERE PN.ItemNumber = PT.ItemNumber;

This assumes PlanName and PlanType have the same number of elements.

Upvotes: 0

Related Questions