insert multiple splited string column to separate rows into a sql table

Question

I have a table as shown below

Is it possible to insert the above table data into a table in separate rows?

I tried using split function on each column and stored each column result on a temp table. I have no clue how to insert into new table combining all these rows and columns as per the id. Any help or suggestion would help.

Answer 1

Here is another method of CTE with help of XML node

There will no need to create any function.

WITH cte AS (
     SELECT ID,
            split.a.value('.', 'NVARCHAR(MAX)') [name],
            ROW_NUMBER() OVER(ORDER BY ( SELECT 1)) RN
     FROM
     (
         SELECT ID,
                CAST('<A>'+REPLACE(name, ';', '</A><A>')+'</A>' AS XML) AS [name]
         FROM <table_name>
     ) a
     CROSS APPLY name.nodes('/A') AS split(a)),
     CTE1 AS (
     SELECT ID,
            split.a.value('.', 'NVARCHAR(MAX)') [title],
            ROW_NUMBER() OVER(ORDER BY ( SELECT 1 )) RN
     FROM
     (
         SELECT ID,
                CAST('<A>'+REPLACE(title, ';', '</A><A>')+'</A>' AS XML) AS [title]
         FROM <table_name>
     ) aa
     CROSS APPLY title.nodes('/A') AS split(a))
     SELECT C.ID, C.name, C1.title FROM CTE C
          JOIN CTE1 C1 ON C1.RN = C.RN
     WHERE C.name != '' AND C1.title != '';

Result :

ID  name title
1   a    12
1   b    13
1   s    45
2   c    67
2   f    56
2   u    34
3   l    90
3   k    70
3   m    60