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.

What have you tried? Where is the code? What about error messages? Please help enable others to help you. A vague plea for help is not going to generate any assistance because we have no idea what you are trying to do. See [this link](https://stackoverflow.com/help/how-to-ask) to learn about asking a question. — Sean Lange, Apr 12 '19 at 16:41
Please provide the column definitions in your source and target as text in your question, rather than as a link to an image. Also include a description of how the data should be parsed - the image that you linked has multiple rows, so we'll need to know what logic drives the decision to create a new row rather than a new column. We're a helpful bunch here, but bear in mind that we don't know what you're doing the way you do, since we haven't been neck-deep in it the way you have been. — Brian, Apr 12 '19 at 16:46

John Cappelletti · Accepted Answer · 2019-04-12T22:13:54.317

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

How to perform text to column on SQL Server

1 Answers1