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.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
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 |
+--------------+------------------------+----------+-----------------+
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.