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:
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
insert into @temp(Items) values(RTRIM(LTRIM(@slice)))
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.
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