Split string (with variable number of value) into different columns

Question

I would like to split a string with a variable number of values into different columns.

I know that It exist a solution 'PIVOT' but I'm not sure this will works when the number of value is not the same.

Here is an example:

+-----+-----------+
| Key |   Value   |
+-----+-----------+
|   1 | A,B,C     |
|   2 | A,B,C,D   |
|   3 | A,B,C,D,E |
+-----+-----------+

Result:

+-----+------+------+------+------+------+
| Key | Col1 | Col2 | Col3 | Col4 | Col5 |
+-----+------+------+------+------+------+
|   1 | A    | B    | C    |      |      |
|   2 | A    | B    | C    | D    |      |
|   3 | A    | B    | C    | D    | E    |
+-----+------+------+------+------+------+

I know that there is at least 1 value and maximum 5 values in the string, so I have to generate 5 columns and filing it accordingly.

Answer 1

Use a bit of XML querying:

DECLARE @mockup TABLE (
    [key] INT
    ,[value] VARCHAR(max)
    );

INSERT INTO @mockup
VALUES 
 (1,'A,B,C')
,(2,'A,B,C,D')
,(3,'A,B,C,D,E')

;WITH Splitted
AS (
    SELECT [key]
        ,[value]
        ,CAST('<x>' + REPLACE([value], ',', '</x><x>') + '</x>' AS XML) AS Parts
    FROM @mockup
    )
SELECT [key]
    ,Parts.value(N'/x[1]', 'varchar(max)') AS Col_1
    ,Parts.value(N'/x[2]', 'varchar(max)') AS Col_2
    ,Parts.value(N'/x[3]', 'varchar(max)') AS Col_3
    ,Parts.value(N'/x[4]', 'varchar(max)') AS Col_4
    ,Parts.value(N'/x[5]', 'varchar(max)') AS Col_5
FROM Splitted;

Result: