11,932,461 members (54,103 online)
Tip/Trick
alternative version

6.1K views
2 bookmarked
Posted

# Using Split Function convert single column to multicolumn in SQL

, 16 Aug 2012 CPOL
 Rate this:
My requirement is to convert a single column of a table to another multi column table with the save data that single column contained.

## Return column wise table

My requirement is to convert a single column of a table to another multi column table with the save data that single column contained. The table structure I have is as follows:

```MidpointId MidPoint
1 3,2,1
2 -2.5,1.2,2
3 12,11,2.9```

I only need the x, y , z coordinates of the above table in different columns. The table required from this table is as:

```PointId PointX PointY PointZ
1 3 2 1
2 -2.5 1.2 2
3 12 11 2.9```

This is a very critical job. After hunting for 3 hours write a Tabled valued function which returns a table as I required. The code I have use to write the SQL table valued function is as:

```-- =============================================
-- Author:
-- Create date: <27,6,2011>
-- Description:
-- =============================================
alter FUNCTION SplitCommaSeperatedToTable(@String nvarchar(MAX), @Delimiter nvarchar(5))
returns @temptable TABLE (startX nvarchar(20),startY nvarchar(20),startZ nvarchar(20))
as
begin
declare @idx int
declare @slice nvarchar(MAX)
declare @temp TABLE (Items nvarchar(20))
declare @startx nvarchar(20)
declare @starty nvarchar(20)
declare @startz nvarchar(20)
declare @counter int
DECLARE @COUNT INT
SET @COUNT=1
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
BEGIN
-- if(@counter=0)
--BEGIN
-- set @counter=@counter+1
insert into @temp(Items) values(RTRIM(LTRIM(@slice)))
--END
--else if(@counter=1)
-- begin
-- insert into @temptable(startX) values(RTRIM(LTRIM(@slice)))
--end
END
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
BEGIN
SELECT @COUNTER=COUNT(*) FROM @TEMP
WHILE @COUNT<=@COUNTER
BEGIN
select @startx = Items from @temp WHERE @COUNT=1
select @starty=Items from @temp WHERE @COUNT=2
select @startz = Items from @temp WHERE @COUNT=3
SET @COUNT=@COUNT+1
END
END
insert into @temptable (startX,startY,startZ) values (@startx,@starty,@startz)
delete from @temptable where startY is null or startZ is null
end
return
end```

All the code above is self explanatory and very simple to understand.

## Share

 Software Developer IDS Ltd India
MCA from GNDU Amritsar 2010 batch pass out.Having 1.3 Years of experience in .net programming with C#.
Earlier worked with Logic Lab Technologies