How to perform text to column on SQL Server

Question

I have a column that includes all the account groups and delimit by space. I want to split all these groups into each new column, similar to the 'Text to Columns' tool on excel that can split a single column of text into multiple columns and assign new column name for each new split column. I tried charindex but seems like there were some errors.

Thank you!

Existing column:

Account Groups
ab bc cd cbd chjk 
abc
ab bc
bc

New columns that I want it to:

Account1 Account2 Account3 Account4 Account5
--------------------------------------------
ab       bc       cd       cbd      chjk
abc
ab       bc
bc

The code I used: I created a function to separate the column first.

CREATE FUNCTION dbo.newCOLUMNS
     (@TEXT VARCHAR(8000),
      @COLUMN TINYINT,
      @SEPARATOR CHAR(1))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @POS_START INT = 1
    DECLARE @POS_END   INT = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

    WHILE (@COLUMN > 1 AND @POS_END > 0)
    BEGIN
        SET @POS_START = @POS_END + 1
        SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
        SET @COLUMN = @COLUMN - 1
    END

    IF @COLUMN > 1  
        SET @POS_START = LEN(@TEXT) + 1

    IF @POS_END = 0 
        SET @POS_END = LEN(@TEXT) + 1

    RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
END

And then I put the function into my table:

SELECT 
    dbo.newCOLUMNS([Name], 1, ' ') as Account1,
    dbo.newCOLUMNS([Name], 2, ' ') as Account2,
    dbo.newCOLUMNS([Name], 3, ' ') as Account3,
    dbo.newCOLUMNS([Name], 4, ' ') as Account4,
    dbo.newCOLUMNS([Name], 5, ' ') as Account5,
    dbo.newCOLUMNS([Name], 6, ' ') as Account6,
    dbo.newCOLUMNS([Name], 7, ' ') as Account7,
    dbo.newCOLUMNS([Name], 8, ' ') as Account8,
    dbo.newCOLUMNS([Name], 9, ' ') as Account9,
    dbo.newCOLUMNS([Name], 10, ' ') as Account10,
    dbo.newCOLUMNS([Name], 11, ' ') as Account11
FROM
    myTable;

The query only able to returns partial results and here is the error message:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Answer 1

Clearly you've learned the lesson not to store delimited data. Now, if you have a known or maximum number of columns, then a little XML may help

Example

Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply (
                Select Pos1  = xDim.value('/x[1]','varchar(max)')
                      ,Pos2  = xDim.value('/x[2]','varchar(max)')
                      ,Pos3  = xDim.value('/x[3]','varchar(max)')
                      ,Pos4  = xDim.value('/x[4]','varchar(max)')
                      ,Pos5  = xDim.value('/x[5]','varchar(max)')
                      ,Pos6  = xDim.value('/x[6]','varchar(max)')
                      ,Pos7  = xDim.value('/x[7]','varchar(max)')
                      ,Pos8  = xDim.value('/x[8]','varchar(max)')
                      ,Pos9  = xDim.value('/x[9]','varchar(max)')
                      ,Pos10 = xDim.value('/x[10]','varchar(max)')
                From  (Select Cast('<x>' + replace([Account Groups],' ','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns