Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Splitting a text string into rows.

Status
Not open for further replies.

Djbell

IS-IT--Management
Apr 22, 2002
175
GB
Hi All

I have a table in my MS 2000 SQL Database server called employees. In this table I have 4 fields and data as follows..

Name Clock Hours Absent
D Jones 001 10 14/08/2007,15/08/2007
D Smith 002 18 13/08/2007,14/08/2007,15/08/2007

What I would like is to create a query that creates a row dor each date so the query would look like the following..

Name Clock Hours Absent
D Jones 001 10 14/08/2007
D Jones 001 10 15/08/2007
D Smith 002 18 13/08/2007
D Smith 002 18 14/08/2007
D Smith 002 18 15/08/2007

Is this possible to do?

Regards

Djbell
 
You can not do it with a 'simple' query, I think. You should create an SP or UDF (it depends on further processing).
 
Correct, a simple query wont work. You need use a function to split the column up. There are several examples in the forum.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Hi

Thanks for the replys..

Can you give me a pointer in the right direction as I have no idea on what I am trying to look for where functions are concerned, and the Search function is undergoing maintenance on this forum at the moment.

Regards

Djbell
 
You'll need to create a table function which uses the substring and charindex within a loop to break out the string into a table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Look in the FAQs in this forum for "Passing a list of variables to a stored procedure" parts 1, 2, and 3.

For what it's worth, putting multiple values in one column is a violation of database normalization rules and is not industry best practice. The problems you're having getting the data out in a usable way are a direct consequence of not being normalized.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Using a UDF for this problem will probably not help because you don't have a single comma delimited list. You have a data set of them.

I would recommend you make use of table variables to hold your data. Then, with some carefully crafted loops, you can successfully get the results you want. I have crafted an example for you. You'll notice that at the top of the example, I am hard coding your sample data in to a table variable named @Original. This means you can copy/paste the code in it's entirety to see how it works. If you are satisfied with the results, simply remove the @Original stuff and replace it with your actual table name.

Code:
[green]-- Setting up sample data[/green]
Declare @Original Table(Name VarChar(20), Clock VarChar(10), Hours int, Absent VarChar(1000))

Insert Into @Original Values('D Jones','001',10,'14/08/2007,15/08/2007')
Insert Into @Original Values('D Smith','002',18,'13/08/2007,14/08/2007,15/08/2007')

[green]-- Query starts here[/green]
Declare @Temp Table(Name VarChar(20), Clock VarChar(10), Hours int, Absent VarChar(1000))

Insert Into @Temp(Name, Clock, Hours, Absent)
Select Name, Clock, Hours, Absent
From   @Original


Declare @Output Table(Name VarChar(20), Clock VarChar(10), Hours int, Absent VarChar(10))


While Exists(Select * From @Temp Where Absent Like '%,%')
  Begin
    Insert Into @Output(Name, Clock, Hours, Absent)
    Select Name, Clock, Hours, Left(Absent, CharIndex(',', Absent)-1)
    From   @Temp
    Where  CharIndex(',', Absent) > 0

    Update @Temp
    Set    Absent = Right(Absent, Len(Absent) - CharIndex(',', Absent))
    Where  CharIndex(',', Absent) > 0

  End

Select Name, Clock, Hours, Absent From @Output
Union All
Select Name, Clock, Hours, Absent From @Temp
Order By Name, Clock, Absent

-George

"the screen with the little boxes in the window." - Moron
 
If you have SQL 2005 you can use a generic UDF using the CROSS APPLY ability.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
The original poster admits to using sql 2000.

-George

"the screen with the little boxes in the window." - Moron
 
Just offering it for consideration to all readers.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
You are doing this hopefully to move to a more normalized structure from the poor design the data currently has?

"NOTHING is more important in a database than integrity." ESquared
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top