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.