Split single column into multiple columns

DECLARE @testvar VARCHAR(MAX)

SELECT @testvar = Coalesce(@testvar + ', ', '') + ColumnName
FROM YourTableName

DECLARE @pos int,@pos2 int, @curruntLocation char(20),@curruntLocation2 char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = @testvar
SELECT @input = @input + ','

CREATE TABLE #tempTable1 (temp1 varchar(100),temp2 varchar(100) )

WHILE CHARINDEX(',',@input) > 0
BEGIN
SELECT @pos=CHARINDEX(',',@input)
SELECT @curruntLocation = RTRIM(SUBSTRING(@input,1,@pos-1))

SELECT @input=SUBSTRING(@input,@pos+1,LEN(@input))
SELECT @pos2=CHARINDEX(',',@input)

SELECT @curruntLocation2 = RTRIM(SUBSTRING(@input,1,@pos2-1))
INSERT INTO #tempTable1 (temp1,temp2) VALUES (@curruntLocation,@curruntLocation2)
SELECT @input=SUBSTRING(@input,@pos2+1,LEN(@input))

END

SELECT * FROM #tempTable1

DROP TABLE #tempTable1