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.

Only via dynamic SQL. Any particular query always returns a result with a fixed "shape" - the number of columns, their names and types. (Dynamic SQL doesn't break this idea - it instead is a way of constructing new queries dynamically) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 2, 2018 at 9:10

Andrea · Accepted Answer · 2018-10-02 06:41:57Z

2

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:

answered Oct 2, 2018 at 6:41

Andrea

12.5k17 gold badges71 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zohar Peled Over a year ago

This is a good solution only if you have a maximum number of columns to return. If the maximum number of columns is unknown when writing the query, then dynamic SQL must be used.

Andrea Over a year ago

@ZoharPeled I agree with you, but apparently the OP has a maximum of 5 columns to return

Zohar Peled Over a year ago

Correct, I didn't see that part in the question before you pointed that out.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related