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

Answer 2

I do not agree with Dinesh script because it is based on RBAR.

I have very similar Split function with also return row_number along with item.

so test my script along with other sample data.

DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,',a,b,c',',12,13,14')

SELECT id
    ,t.RowVal
    ,a.RowVal
FROM (
    SELECT t.id
        ,a.RowNum
        ,a.RowVal
        ,t.TITLE
    FROM @Table t
    CROSS APPLY (
        SELECT *
        FROM dbo.FN_SPLIT_VALUE(t.NAME)
        ) a
    ) t
CROSS APPLY (
    SELECT *
    FROM dbo.FN_SPLIT_VALUE(t.TITLE)
    WHERE t.RowNum = RowNum
    ) a
WHERE t.RowVal <> ''

Answer 3

You can achieve this by writing a table-valued function that will split the strings according to your requirements. Once you have created this object, then you can use the T-SQL in second code snippet to get your final required table.

The definition of this table-valued function is as given below. Just copy and paste this into SSMS and run it against your database.

Split a string function

-- =============================================
-- Author:      B Vidhya
-- Create date: Nov 7, 2017
-- Description: Splits a string and returns a table
-- =============================================
CREATE FUNCTION [dbo].[SplitAString]
(    
      @string NVARCHAR(MAX),
      @delimiter CHAR(1)
)
RETURNS @splitTable TABLE (
      ItemNumber INT IDENTITY(1,1),
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @startIndex INT,@endIndex INT

      SET @startIndex = 1
      IF SUBSTRING(@string, LEN(@string) - 1, LEN(@string)) <> @delimiter
      BEGIN
            SET @string = @string + @delimiter
      END

      WHILE CHARINDEX(@delimiter, @string) > 0
      BEGIN
            SET @endIndex = CHARINDEX(@delimiter, @string)

            INSERT INTO @splitTable(Item)
            SELECT SUBSTRING(@string, @startIndex, @endIndex - 1)

            SET @string = SUBSTRING(@string, @endIndex + 1, LEN(@string))
      END

      RETURN
END

GO

In T-SQL below, I have called the original table StackOverflowTable1 and you can replace this table name with your actual table name. Also, I am inserting final rows into a table variable. If you wanted to insert into your custom table, then you could use perform an INSERT into your table after the END of WHILE loop.

T-SQL to get your final table

DECLARE @myTable TABLE
(Id    INT,
 Name  VARCHAR(5000),
 Title VARCHAR(5000)
);

DECLARE @lastId INT= 0, @id INT, @name VARCHAR(5000), @title VARCHAR(5000);

--for each record in table perform splitting and insertion in new table
WHILE EXISTS
(
    SELECT 1
    FROM StackOverFlowTable1 soft
    WHERE Id > @lastId
)
    BEGIN
        SELECT TOP (1) @id = Id,
                       @name = Name,
                       @title = Title
        FROM StackOverFlowTable1 soft
        WHERE Id > @lastId
        ORDER BY Id;
        SET @lastId = @id;
        INSERT INTO @myTable
        (Id,
         Name,
         Title
        )
               SELECT @id,
                      ss1.Item,
                      ss2.Item
               FROM dbo.SplitString(@name, ';') ss1
                    INNER JOIN dbo.SplitString(@title, ';') ss2 ON ss1.ItemNumber = ss2.ItemNumber
               WHERE ss1.Item <> ''
                     AND ss2.Item <> '';
    END;

SELECT * FROM @myTable;

Answer 4

Try below way .. this will save time and memory also!

This T-SQL block has dependency on dbo.SplitString function ..
T1 is my Source table
T2 is my Destination table

DECLARE @c_s AS CURSOR;
DECLARE @id INT;
DECLARE @name VARCHAR(1000);
DECLARE @value VARCHAR(1000);

SET @c_s = CURSOR FOR SELECT * FROM T1; 
OPEN @c_s;
FETCH @c_s INTO @id, @name, @value

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO T2
    SELECT @id
        , a.Value NAME
        , b.value value
    FROM dbo.SplitString(@name, ';') a
    INNER JOIN dbo.SplitString(@value, ';') b
        ON a.OrdinalPosition = b.OrdinalPosition

  FETCH NEXT FROM @c_s INTO @id, @name, @value
END

here is the dbo.SplitString

CREATE FUNCTION [dbo].[SplitString](@givenString VARCHAR(8000) , @separator VARCHAR(100))
RETURNS TABLE AS
RETURN (
        WITH data([start], [end]) AS (
                SELECT 0 AS [start]
                    , CHARINDEX(@separator, @givenString) AS [end]
                UNION ALL
                SELECT [end] + 1
                    , CHARINDEX(@separator, @givenString, [end] + 1)
                FROM data
                WHERE [end] > 0
                )
        SELECT ROW_NUMBER() OVER (
                ORDER BY OrdinalPosition
                ) OrdinalPosition
            , RTRIM(LTRIM(Value)) Value
        FROM (
            SELECT ROW_NUMBER() OVER (
                    ORDER BY [start]
                    ) OrdinalPosition
                , SUBSTRING(@givenString, [start], COALESCE(NULLIF([end], 0), len(@givenString) + 1) - [start]) Value
            FROM data
            ) r
        WHERE RTRIM(Value) <> ''
            AND Value IS NOT NULL
        )

Answer 5

Try this answer. Hope this helps you.

DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,';a;b;c',';12;13;14')

DECLARE @ID INT=1

SELECT @ID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN1 INTO #T1 FROM dbo.split((SELECT NAME FROM @Table WHERE id=@ID),';')
SELECT @ID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN2 INTO #T2 FROM dbo.split((SELECT TITLE FROM @Table WHERE id=@ID),';')

SELECT T1.ID,T1.Items NAME,T2.Items TITLE
FROM #T1 T1 INNER JOIN #T2 T2 ON T1.RN1=T2.RN2 

DROP TABLE #T1
DROP TABLE #T2

If you want all the values, you just try the looping method like WHILE.

DECLARE @Table TABLE(ID INT, NAME VARCHAR(10),TITLE VARCHAR(10))
INSERT INTO @Table VALUES (1,';a;b;c',';12;13;14'),(2,';c;f;u',';67;56;34'),(3,';l;k;m',';90;70;60')

DECLARE @MinID INT,@MaxID INT
SELECT @MinID=MIN(ID),@MaxID=MAX(ID) FROM @Table

CREATE TABLE #T1(ID INT,Items VARCHAR(10),RN1 INT)
CREATE TABLE #T2(ID INT,Items VARCHAR(10),RN2 INT)

WHILE @MinID<=@MaxID
BEGIN
    INSERT  INTO #T1
    SELECT @MinID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN1 
    FROM dbo.split((SELECT NAME FROM @Table WHERE id=@MinID),';')

    INSERT  INTO #T2
    SELECT @MinID ID,Items,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN2 
    FROM dbo.split((SELECT TITLE FROM @Table WHERE id=@MinID),';')

    SET @MinID=@MinID+1
END

    SELECT T1.ID,T1.Items NAME,T2.Items TITLE
    FROM #T1 T1 INNER JOIN #T2 T2 ON T1.ID=T2.ID AND T1.RN1=T2.RN2 

DROP TABLE #T1
DROP TABLE #T2

This will produce the result, what you exactly want:

ID          NAME       TITLE
----------- ---------- ----------
1           a          12
1           b          13
1           c          14
2           c          67
2           f          56
2           u          34
3           l          90
3           k          70
3           m          60

Here is the split function, I used to split the Strings:

CREATE FUNCTION [dbo].[Split]
(@String VARCHAR (max), @Delimiter CHAR (1))
RETURNS 
    @temptable TABLE (
        [items] VARCHAR (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
AS
begin        
    declare @idx int        
    declare @slice varchar(max)        

    select @idx = 1        
        if len(@String)<1 or @String is null  return        

    while @idx!= 0        
    begin        
        set @idx = charindex(@Delimiter,@String)        
        if @idx!=0        
            set @slice = left(@String,@idx - 1)        
        else        
            set @slice = @String        

        if(len(@slice)>0)   
            insert into @temptable(Items) values(@slice)        

        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return        
end