T-SQL One column in multiple columns select query

Question

I have a simple problem that I have not been able to find a solution to and I'm hoping someone on StackOverflow can help.

I currently have an example query as shown below

SELECT ID
     , ColumnName
  FROM Table

If I run this query I get the following result:

==================
ID | ColumnName
------------------
 1 | One_Two_Three
 2 | Four_Five_Six
==================

The result I'm after is as follows:

========================
ID | Col1 | Col2 | Col3    
------------------------
 1 | One  | Two  | Three
 2 | Four | Five | Six
========================

Your assistence is appreciated.

this []http://stackoverflow.com/questions/2647/split-string-in-sql Should help — apomene, Mar 14 '14 at 09:38

score 0 · Answer 1 · answered Mar 14 '14 at 09:41

Have a look at this example

DECLARE @Table1 TABLE 
    ([ID] int, [ColumnName] varchar(13))


INSERT INTO @Table1
    ([ID], [ColumnName])
VALUES
    (1, 'One_Two_Three'),
    (2, 'Four_Five_Six')


;WITH Vals AS (
        SELECT  *,
                CAST('<d>' + REPLACE([ColumnName], '_', '</d><d>') + '</d>' AS XML) ColumnValue
        FROM    @Table1
)
SELECT  v.*,
        A.B.value('.', 'varchar(max)')
FROM    Vals v CROSS APPLY
        ColumnValue.nodes('/d') A(B)

T-SQL One column in multiple columns select query

1 Answers1

SQL Fiddle DEMO