Click here to Skip to main content
11,415,164 members (65,426 online)
Click here to Skip to main content

Using Split Function convert single column to multicolumn in SQL

, 16 Aug 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Balwant.mnd
Software Developer IDS Ltd
India 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

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150427.4 | Last Updated 16 Aug 2012
Article Copyright 2012 by Balwant.mnd
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid