I have a TSQL (MSSQL) table which contains record in below format

I want to split the records in below format

Column1 and Column2 maintain relationship using the "/" separator and related to each other in same sequence.

I tried to split the column with help of CHARINDEX & SUBSTRING but I am not able to maintain relationship between two columns.

CodePudding user response：

You can add a function to split strings.

Then cross apply to the split parts of Column1 and Column2.

create table test (
 Id int identity primary key, 
 Column1 varchar(30),
 Column2 varchar(30)
);

insert into test (Column1, Column2) values
('a/b/c', 'apple/banana/cucumber'), 
('d/e/f', 'orange/prune/onion');

(UDF copied from here )

CREATE FUNCTION dbo.fnString_Split
(
    @str    nvarchar(4000), 
    @delim  nchar(1)
)
RETURNS TABLE 
WITH SCHEMABINDING 
AS 
RETURN
(
  WITH RCTE AS (
    SELECT 
      1 AS ordinal
    , ISNULL(NULLIF(CHARINDEX(@delim, @str),0), LEN(@str)) AS pos
    , LEFT(@str, ISNULL(NULLIF(CHARINDEX(@delim, @str),0)-1, LEN(@str))) AS value
    UNION ALL
    SELECT 
      ordinal 1
    , ISNULL(NULLIF(CHARINDEX(@delim, @str, pos 1), 0), LEN(@str))
    , SUBSTRING(@str, pos 1, ISNULL(NULLIF(CHARINDEX(@delim, @str, pos 1),0)-pos-1, LEN(@str)-pos )) 
    FROM RCTE
    WHERE pos < LEN(@str)
  ) 
  SELECT ordinal, value
  FROM RCTE
);

select 
  t.Id
, ca.Column1
, ca.Column2
from test t
cross apply (
  select 
    s1.ordinal
  , s1.value as Column1
  , s2.value as Column2
  from dbo.fnString_Split(t.Column1,'/') as s1
  join dbo.fnString_Split(t.Column2,'/') as s2
    on s1.ordinal = s2.ordinal
) ca;

Demo on db<>fiddle here

CodePudding user response：

Please try the following solution.

It is JSON based, and will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT, ColB varchar(8000), ColC varchar(8000));
INSERT INTO @tbl VALUES
(1,'a/b/c','apple/banana/cucumber');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '/';

WITH rs AS 
(
     SELECT *
         , ar1 = '["'   REPLACE(ColB, @separator, '","')   '"]'
         , ar2 = '["'   REPLACE(ColC, @separator, '","')   '"]'
     FROM @tbl
 )
 SELECT ID, ColB.[value] AS [ColB], ColC.[value] AS ColC
 FROM rs
    CROSS APPLY OPENJSON (ar1, N'$') AS ColB
    CROSS APPLY OPENJSON (ar2, N'$') AS ColC
 WHERE ColB.[key] = ColC.[key];

Output

 ---- ------ ---------- 
| ID | ColB |   ColC   |
 ---- ------ ---------- 
|  1 | a    | apple    |
|  1 | b    | banana   |
|  1 | c    | cucumber |
 ---- ------ ---------- 

CodePudding user response：

1. First create the function below to split the string.
2. Then, execute the code that follows the function code.

    -- Function Code
    CREATE FUNCTION [dbo].[udf_SplitList]
        (   
                @InputString    varchar(MAX)    
            ,   @Separator      varchar(1)  
        )
        RETURNS @ValuesList TABLE ( ID int IDENTITY(1,1), Value varchar(MAX))
        AS
            BEGIN
                DECLARE @ListValue NVARCHAR(max)
                SET @InputString = @InputString   @Separator
                WHILE (LEN(@InputString) > 0)
                    BEGIN
                        SELECT @ListValue = SUBSTRING(@InputString , 1, CHARINDEX(@Separator, @InputString) - 1)
                        INSERT INTO @ValuesList
                        SELECT LTRIM(@ListValue)
                        SELECT @InputString = SUBSTRING(@InputString, CHARINDEX(@Separator, @InputString)   1 , LEN(@InputString) - CHARINDEX(@Separator, @InputString)) 
                END 
                RETURN 
            END

    -- Execution Code
    DECLARE     @YourTable TABLE (ID int, CodeList varchar(MAX), ValueList varchar(MAX));
    INSERT INTO @YourTable  VALUES ( 1, 'a/b/c', 'apple/banana/cucumber');
    SELECT  X.*
    FROM    @YourTable  Y
    CROSS APPLY
        (
            SELECT 
                    Code  = C.Value
                ,   Value = V.Value
            FROM    dbo.udf_SplitList(Y.CodeList , '/') C
            JOIN    dbo.udf_SplitList(Y.ValueList, '/') V ON V.ID = C.ID
        ) X
    ;