Split String Column To Multi Column in SQL

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..,

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

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