Pfthb

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

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.

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.