December 17, 2008 at 9:01 am
Hi All,
I need a help in the following:
My table has a column as comma separated sting.
I want to split the values and return the result as column along with existing column.
col1 col2 col3
1 2 test,to,split
2 5 test1,to1,split1
and I want the result as,
1,2,test,to,split
2,5,test1,to1,split1
Is it possible to do this?
Thanks for your help.
-Maria
December 17, 2008 at 9:22 am
yes this is possible,
you will need to use the Substring funciton to split out the CSV values.
haev a look at this article for an efficent way of dealing with this..
http://www.sqlservercentral.com/articles/TSQL/62867/"> http://www.sqlservercentral.com/articles/TSQL/62867/
December 17, 2008 at 9:23 am
I use a function to turn comma separated lists in varchars into tables (don't ask - i know it breaks 1st nornal form - it's something i inherited)
you should be able to modify this to pass in your primary key as well
I haven't tried using this table value function as part of an inner join like this, but it's worth a try
CREATE FUNCTION fn_GetTableByIDList (@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@STR nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @STR = substring(@tmpstr, 1, @pos - 1)
INSERT @tbl (number) VALUES(convert(int, @STR))
SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
IF ltrim(rtrim(@leftover)) <> ''
INSERT @tbl (number) VALUES(convert(int, @leftover))
RETURN
END
MVDBA
December 17, 2008 at 9:56 am
Hi Steve,
Thanks for your reply.
I could get the comma separated values as rows. How to pivot that into columns?
I am very new to this sqlserver.
Thanks for your help.
-Maria
December 17, 2008 at 10:02 am
Hi Micheal,
When I try to call the function like this,
select * from dbo.fn_GetTableByIDList('test,to,split',',')
I am getting the following error:
Conversion failed when converting the nvarchar value 'test' to data type int.
Thanks for your help.
-Maria
December 17, 2008 at 10:14 am
are there same number of commas in col3 for every row and how is your output going to be used?
December 17, 2008 at 10:18 am
Hi,
Thanks for helping me.
Yes, there are same number of commas for the given query.
Thanks,
Maria
December 17, 2008 at 12:40 pm
How are you going to use the result set?
December 17, 2008 at 12:54 pm
Hi,
I want to display that data in a gridview and pass that table
for further calculations .
-Maria
December 17, 2008 at 1:07 pm
Personally, I would return all columns as one comma separated string
(select col1+','+ col2 + ',' + col3 from yourtable)
and parse it in the client.
Returning a dataset from the SQL server will most likely require you to create a stored proc and loop thru a cursor.
December 17, 2008 at 1:51 pm
I know this is SS forum but here is one way of doing it on the client.
if (!IsPostBack)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBconnString"].ToString());
SqlCommand comm = new SqlCommand("select col1+','+col2+','+col3 as line from yourtable", conn);
conn.Open();
SqlDataReader rdr = comm.ExecuteReader();
//assuming there are 2 + 3 = 5 cols now
DataTable dt = new DataTable();
if (rdr.HasRows)
{
dt.Columns.Add("col1", typeof(string));
dt.Columns.Add("col2", typeof(string));
dt.Columns.Add("col3", typeof(string));
dt.Columns.Add("col4", typeof(string));
dt.Columns.Add("col5", typeof(string));
while (rdr.Read())
{
string[] cols = rdr.GetString(0).Split(new char[] { ',' });
DataRow dr = dt.NewRow();
for (int i=0;i<cols.Length;i++)
{
dr = cols;
}
dt.Rows.Add(dr);
}
}
rdr.Close();
conn.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
December 17, 2008 at 7:20 pm
michael vessey (12/17/2008)
I use a function to turn comma separated lists in varchars into tables (don't ask - i know it breaks 1st nornal form - it's something i inherited)
Nicely done. Looks very similar to Sergiy's and Adam Machanic's work. Consider this, though... in SQL Server 2005, there's no longer a need to use the TEXT data type. In fact, it has been officially deprecated. That also means that there's no longer a need for a WHILE loop in the code to handle the deficiencies of the TEXT data type.
With that in mind, consider the following as a suggestion to an alternate...
Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays
[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2008 at 3:24 am
sorry - the original function was designed to split integers - you should be able to modify the code so output a list of varchars rather than int
MV
MVDBA
December 18, 2008 at 8:39 am
Hi Jeff,
I tried your tally table and splitting the string as rows in a table output.
But how to make them as columns? or use crosstab to make it as a column?
For eg.
"test,to split" returns as 3 rows
test
to
split
But I want it as
test to split - as a 3 column data along with other entries
Thanks,
Maria
December 18, 2008 at 9:30 am
Maria,
If your table has a lot of records , you should write a stored proc and use a cursor. if not here is your function.
this is how you would use it
SELECT
col1
,col2
,[dbo].[colsplit](col3,0)
,[dbo].[colsplit](col3,1)
,[dbo].[colsplit](col3,2)
from yourtable
CREATE FUNCTION colsplit
(
@colval nvarchar(50),
@which int
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE @pos int,
@len int
set @pos = 0
while (@which > 0)
begin
set @pos = charindex(',',@colval,@pos+1)
set @which = @which - 1
end
set @len = charindex(',',@colval,@pos+1)
set @len = @len - @pos - 1
if @len > 0
RETURN substring(@colval,@pos+1,@len)
RETURN @colval
END
GO
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply