split content of each row and put it into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

split content of each row and put it into a table

Author

Topic

ram_kri
Starting Member

2 Posts

Posted - 2007-07-05 : 07:16:01

Hi,
I have this following situation and how to create a query for this :

I have a table:

Desc
----
Columns : ID, DESCRIPTION

Let's say we have 3 rows in the table :

1 abc,def,ghi
2 jklmn,opq,rrsstuvew
3 xyz,aaaa,bbbbbbbbb,ddddddd,eeeeee

Now out of that table I want to create a table that has :

NewDesc
-------
Columns : ID, DescriptionSplited

1 abc
1 def
1 ghi
2 jklmn
2 opq
2 rrsstuvew
3 xyz
3 aaaa
3 bbbbbbbbb
3 ddddddd
3 eeeeee

I dont mind if we have to insert/update the exisiting table or move it into a new table.

Idea is to retain the id, but split the description based on coma and put it in a different row.
So that there wont be comas anywhere in the table.

I have written a split function for the same :
as below :
/////////////////////////////////////////////////////////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN

While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End

Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))

Return
END
/////////////////////////////////////////////////////////////////////

Now using this I want to write a query to achieve the above task.
Any help in this regard would be great.

Thanks in advance.

Regards,
Kris

Kristen
Test

22859 Posts

Posted - 2007-07-05 : 07:17:47

Not sure this specifically covers your problem, but it may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting%20delimited%20lists,Best%20split%20functions

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 07:19:56

use CROSS APPLY


select t.ID, s.Value
from   table t cross apply split(t.DESCRIPTION) s

KH
[spoiler]Time is always against us[/spoiler]

ram_kri
Starting Member

2 Posts

Posted - 2007-07-05 : 08:04:51

Maaaaaan 'Khtan" !!!
You rock !!
It worked..never thought it would this simple.
Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-05 : 08:10:09

If you are not using SQL Server 2005, it will not be that simple

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 09:43:14

Also refer
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources