Split String Column To Multi Column in SQL

Question

I have the following table:

 Code |           Column
 -----+-----------------------------
 152  |    12_25_62_26_65_22_88
 247  |    55_56_85_52_11_45_63
 369  |    45_65_25_89_52_54_96

How can I write a SELECT query that obtains a result like this?:

 Code | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7
 -----+------+------+------+------+------+------+-------
 152  |  12  |  25  |  62  |  26  |  65  |  22  |  88
 247  |  55  |  56  |  85  |  52  |  11  |  45  |  63
 369  |  45  |  65  |  25  |  89  |  52  |  54  |  96

This question seems to be already answered: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Leni, Mar 04 '18 at 18:57
Can we safely *assume* that the number of columns is limited to only being 7? Could it be less, more? — Thom A, Mar 04 '18 at 19:27
@Dakshitha You are right, this might be closed as duplicate, but I tend to answer such a question. In this case the highest rated answers are absolutely outdated. There are good answers but people tend to take the first/highest so bad and outdated approaches are kept alive... — Shnugo, Mar 04 '18 at 19:59

score 6 · Answer 1 · answered Mar 04 '18 at 19:50

I'd put it this way:

DECLARE @mockup TABLE (Code INT, [Column] VARCHAR(100));
INSERT INTO @mockup VALUES
(152 , '12_25_62_26_65_22_88'),
(247 , '55_56_85_52_11_45_63'),
(369 , '45_65_25_89_52_54_96');

WITH Splitted AS
(
    SELECT Code
          ,[Column] 
          ,CAST('<x>' + REPLACE([Column],'_','</x><x>') + '</x>' AS XML) AS Parts 
    FROM @mockup
)
SELECT Code,[Column]
      ,Parts.value(N'/x[1]','int') AS Col1
      ,Parts.value(N'/x[2]','int') AS Col2
      ,Parts.value(N'/x[3]','int') AS Col3
      ,Parts.value(N'/x[4]','int') AS Col4
      ,Parts.value(N'/x[5]','int') AS Col5
      ,Parts.value(N'/x[6]','int') AS Col6
      ,Parts.value(N'/x[7]','int') AS Col7
FROM Splitted;

The trick to split a string via XML allows to address each element directly with its position. This is especially usefull, if the string contains various types (not in your case, where all values are int). You can retrieve all of them type safe and rather easily.

Attention This is a bad design. If you can change this, you should really avoid to store more than one value within one column..,

Clearly the sage approach – John Cappelletti Mar 04 '18 at 20:14 — John Cappelletti, Mar 04 '18 at 20:14

score 2 · Answer 2 · answered Mar 04 '18 at 19:11

You can use this.

DECLARE @MyTable TABLE (Code INT, [Column] VARCHAR(100))
 INSERT INTO @MyTable VALUES
 (152 , '12_25_62_26_65_22_88'),
 (247 , '55_56_85_52_11_45_63'),
 (369 , '45_65_25_89_52_54_96')



SELECT Code, [1] Col1, [2] Col2, [3] Col3, [4] Col4, [5] Col5, [6] Col6, [7] Col7 FROM (
    select Code, Rn, Col from ( 
        select Code, CAST('<Root><row>' + REPLACE([Column],'_','</row><row>') + '</row></Root>'  as XML) [ColumnXml] from @MyTable
    ) T
    outer apply (select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) RN, T.c.value( '.', 'integer') from T.[ColumnXml].nodes('/Root/row') T(c) ) ColTable(Rn, Col)
) SRC PIVOT( MAX(Col) FOR RN IN ([1],[2],[3],[4],[5],[6],[7])) PVT

Result:

Code        Col1        Col2        Col3        Col4        Col5        Col6        Col7
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
152         12          25          62          26          65          22          88
247         55          56          85          52          11          45          63
369         45          65          25          89          52          54          96

score 2 · Answer 3 · answered Mar 04 '18 at 19:57

You can use dynamic SQL to achieve this:

DECLARE @MyTable TABLE (Code INT, [Column] VARCHAR(100))
INSERT INTO @MyTable VALUES
(152 , '12_25_62_26_65_22_88'),
(247 , '55_56_85_52_11_45_63'),
(369 , '45_65_25_89_52_54_96')

declare @query nvarchar(1000)
set @query = ''
select @query = @query + 'select ' + cast(code as nchar(3)) + ',' + replace([column],'_',',') + ' union all ' from @MyTable
--remove last ten characters (union all statement)
set @query = left( @query, len(@query) - 10)
execute sp_executesql @query

Split String Column To Multi Column in SQL

3 Answers3