Split multiple multi-value columns between special characters into rows

Question

Hi there, I've found many sources on how to split a single multi-value column into rows but I'm not finding how to split multiple multi-value columns into rows.

I have a table shown in the image below and I would love to know the simplest way in SQL to split both columns to match the 1st value in each field, 2nd value in each field, etc.
![248340-list.png][1]

Here is a select statement to create the example list.

CREATE TABLE #List (
ID VARCHAR (50)
, Descr VARCHAR (100)
, ItemCode VARCHAR (4000)
, RetDesc VARCHAR (4000)
)

INSERT INTO #List
( ID, Descr, ItemCode, RetDesc )
VALUES
('AccountTypes', 'CUSTOMER ACCOUNT TYPES', 'L^A', 'LOANS^ARREARS'),
('ActionType', 'ACTION TYPE', 'E^A^U^D^T^R', 'ENQUIRE^AMEND^AUTHORIZED^DELETED^TERMINATED^ARCHIVE RECALLED');

SELECT *
FROM #List

Answer 1

Hi @Julie Miller ,

A minimal reproducible example is not provided. So, I am shooting from the hip.

Please try the following solution.
It is based on JSON. It will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start  
DECLARE @list TABLE  (ID VARCHAR (50), Descr VARCHAR (100), ItemCode VARCHAR (4000), RetDesc VARCHAR (4000));  
INSERT INTO @List  
(ID, Descr, ItemCode, RetDesc ) VALUES  
('AccountTypes', 'CUSTOMER ACCOUNT TYPES', 'L^A', 'LOANS^ARREARS'),  
('ActionType', 'ACTION TYPE', 'E^A^U^D^T^R', 'ENQUIRE^AMEND^AUTHORIZED^DELETED^TERMINATED^ARCHIVE RECALLED');  
-- DDL and sample data population, end  
  
SELECT *  
FROM @List;  
  
DECLARE @separator CHAR(1) = '^';  
  
;WITH rs AS   
(  
     SELECT ID, Descr  
         , s = '["' + REPLACE(ItemCode, @separator, '","') + '"]'  
         , s1 = '["' + REPLACE(RetDesc, @separator, '","') + '"]'  
     FROM @list  
 )  
 SELECT ID, Descr, ItemCode.[value] AS ItemCode, RetDesc.[value] AS RetDesc  
 FROM rs  
    CROSS APPLY OPENJSON (s, N'$') AS ItemCode  
    CROSS APPLY OPENJSON (s1, N'$') AS RetDesc  
 WHERE ItemCode.[key] = RetDesc.[key];  

Output

+--------------+------------------------+----------+-----------------+  
|      ID      |         Descr          | ItemCode |     RetDesc     |  
+--------------+------------------------+----------+-----------------+  
| AccountTypes | CUSTOMER ACCOUNT TYPES | L        | LOANS           |  
| AccountTypes | CUSTOMER ACCOUNT TYPES | A        | ARREARS         |  
| ActionType   | ACTION TYPE            | E        | ENQUIRE         |  
| ActionType   | ACTION TYPE            | A        | AMEND           |  
| ActionType   | ACTION TYPE            | U        | AUTHORIZED      |  
| ActionType   | ACTION TYPE            | D        | DELETED         |  
| ActionType   | ACTION TYPE            | T        | TERMINATED      |  
| ActionType   | ACTION TYPE            | R        | ARCHIVE RECALLED |  
+--------------+------------------------+----------+-----------------+  

Answer 2

You need a string splitter that returns the list position and then you would join on the listposition.

See this short article on my web site for such string splitters; https://www.sommarskog.se/arrays-in-sql.html.

Answer 3

Hi @Julie Miller
You could create a User-Defined Function to get split values with identity number.
Please check this:

CREATE FUNCTION [dbo].[split_value]  
(  
 @string NVARCHAR(MAX),  
 @delimiter NVARCHAR(10)  
)   
RETURNS @t TABLE (id INT IDENTITY(1,1),row_values NVARCHAR(MAX))  
AS  
  BEGIN  
    DECLARE @xml XML  
    SET @xml = N'<t>' + REPLACE(@string,@delimiter,'</t><t>') + '</t>'  
    INSERT INTO @t(row_values)   
	SELECT v.value('.','nvarchar(MAX)') AS item FROM @xml.nodes('/t') as vals(v)  
    RETURN  
  END  
GO  
        
SELECT L.ID,L.Descr,I.row_values AS ItemCode,R.row_values AS RetDesc  
FROM #List L  
  CROSS APPLY [dbo].[split_value](L.ItemCode,'^') I  
  CROSS APPLY [dbo].[split_value](L.RetDesc,'^') R  
WHERE I.id=R.id  

Output:

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.