Can we apply String_Split function on multiple columns in SQL?

Question

Here I have Three columns of table. I have implemented String_Split on 'YearofWinner' column using Cross Apply, i have another column 'HostsofYear' column with multiple values.
How can we apply String_Split function on two columns in a table

id Country_Name YearofWinner HostsofYear
1 Uruguay 1930|1950 1930,1950
2 Italy 1934|1938|1982|2006 1934,1990
3 Germany 1954|1974|1990|2014 NULL
4 Brazil 1958|1962|1970|1994|2002 NULL
5 England 1966 NULL
6 Argentina 1978|1986 NULL
7 France 1998|2018 1938,1998
8 Spain 2010 NULL

Answer 1

Hi @Ramana Kopparapu
Not sure what you want, but check this query:

CREATE TABLE #TEST1(id INT,Country_Name VARCHAR(20),YearofWinner VARCHAR(50), HostsofYear VARCHAR(50))  
INSERT INTO #TEST1 VALUES  
(1,'Uruguay','1930|1950','1930,1950'),  
(2 ,'Italy','1934|1938|1982|2006','1934,1990'),  
(3 ,'Germany','1954|1974|1990|2014',NULL),  
(4 ,'Brazil','1958|1962|1970|1994|2002',NULL),  
(5 ,'England','1966',NULL),  
(6 ,'Argentina','1978|1986',NULL),  
(7 ,'France','1998|2018','1938,1998'),  
(8 ,'Spain','2010',NULL)  
  
;WITH CTE1 AS  
(  
 SELECT id,Country_Name,V.VALUE AS YearofWinner,ROW_NUMBER()OVER(PARTITION BY id ORDER BY YearofWinner) RNum1  
 FROM #TEST1 t CROSS APPLY STRING_SPLIT(t.YearofWinner,'|') V  
),CTE2 AS  
(  
 SELECT id,Country_Name,V.VALUE AS HostsofYear,ROW_NUMBER()OVER(PARTITION BY id ORDER BY HostsofYear) RNum2  
 FROM #TEST1 t CROSS APPLY STRING_SPLIT(t.HostsofYear,',') V  
)  
SELECT C1.ID,C1.Country_Name,C1.YearofWinner,c2.HostsofYear  
FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.ID=C2.ID and C1.RNum1=C2.RNum2   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

 --SQL Server 2022 STRING_SPLIT with ordinal option  
 ;with mycte1 as (  
 select * from #TEST1 t   
 outer APPLY STRING_SPLIT(t.YearofWinner,'|',1) V1   
 )  
,mycte2 as (  
 select * from #TEST1 t   
   outer APPLY   STRING_SPLIT(t.HostsofYear,',',1)v  
 )  
 select m1.id,m1.Country_Name  
 ,m1.value YearofWinner  
 ,m2.value HostsofYear  
   
from mycte1 m1 left join mycte2 m2 on m1.id=m2.id and m1.ordinal=m2.ordinal