create semicolon seperated values as Rows

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)

create semicolon seperated values as Rows

Author

Topic

keentolearn
Starting Member

21 Posts

Posted - 2009-07-13 : 12:25:13

Hi,

I have a list people and record IDs in Excel:

MyTable
RecordId - Team
1000 - Smith, John; Brown, Matt
1010 - Halls, G; Jones, Tom; Michael, Jason

I want to create a table in SQL capturing the record IDs and returning each Team value as a new row e.g:

RecordID - Team
1000 - Smith, John
1000 - Brown, Matt
1010 - Halls, G
1010 - Jones, Tom
1010 - Michael, Jason

and Look Up these values in the database table People, if they don't exist in the People > create them.

I have the following table function named ListToTable:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[listToTable](@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
Position int,
Value varchar(8000)
)
AS
BEGIN
declare @myPos int
set @myPos = 1

while charindex(@delim, @list) > 0
begin
insert into @listTable(Position, Value)
values(@myPos, left(@list, charindex(@delim, @list) - 1))

set @myPos = @myPos + 1
if charindex(@delim, @list) = len(@list)
insert into @listTable(Position, Value)
values(@myPos, '')
set @list = right(@list, len(@list) - charindex(@delim,
@list))
end

if len(@list) > 0
insert into @listTable(Position, Value)
values(@myPos, @list)

RETURN
END

so if i write:
SELECT * FROM [listToTable]
('jones; tom',';')

I get the following result:
1 jones
2 tom

However, i am not sure how to use the Listtotable function to get the data from my excel list?

Hope this makes sense.

Many thanks in advance.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-13 : 12:31:57

sounds like a job for PIVOT

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

keentolearn
Starting Member

21 Posts

Posted - 2009-07-13 : 13:18:57

Ok. I hope I can ask the question properly now!

I want to create a process in SQL which will:
insert a new row for each multi-value field (values are seperated by semicolon). e.g.
Table1
RecordId - Team
1000 - Smith, John; Brown, Matt
1010 - Halls, G; Jones, Tom; Michael, Jason
1015 - Brown, Olly

should be changed to:

RecordID - Team
1000 - Smith, John
1000 - Brown, Matt
1010 - Halls, G
1010 - Jones, Tom
1010 - Michael, Jason

the second part of my question perhaps should be a new post but it is for after updating Table1 in the above format, I would like to check the values in Table1 with values in Table2 and get the person IDs from Table2. If names dont exists in Table2, create them and then get the IDs.

Table2
PersonID, Name
400, Smith, John
401, Brown, Matt

Table1 should have the IDs from Table2 inserted:
Table1
RecordID - Team - PersonID
1000 - Smith, John - 400
1000 - Brown, Matt - 401 etc..

I have many fields where there are multi values for each record (like each record can have many people data, clients, countries..and we have picklists for these on the database. When migrating data from an external source e.g.excel, I do not want to create duplicate Country names or People names but select them from appropriate tables).

Please let me know if you need further clarification.

Many thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 13:34:54

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

SELECT mt.RecordID, f.Data FROM MyTable AS mt
CROSS APPLY dbo.fnParseList(';', mt.Team) AS f

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 14:03:50

have you heard of normailzation? why storing multiple values in same fields?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-13 : 14:23:29

normalization is not enterprise level programming. get with it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

keentolearn
Starting Member

21 Posts

Posted - 2009-07-17 : 07:20:11

Hi Peso, I have tried using the dbo.fnParseList but it didnt work quite right e.g.
RecordID 1000962 has three team members in one cell which after running the script should return:
1000962 Parker, Tom
1000962 Brown, Tim
1000962 Smith, Jon

instead it returns:
1000962 Parker, Tom;Brown, Tim;Smith, Jon
1000962 Parker, Tom;Brown, Tim;Smith, Jon
1000962 Parker, Tom;Brown, Tim;Smith, Jon

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-17 : 10:37:39

Is this of any help...

CREATE FUNCTION TEST(@STR VARCHAR(MAX))
RETURNS @TMPTABLE TABLE
(NAME VARCHAR(MAX))
AS
BEGIN

DECLARE @TEMP VARCHAR(MAX),@PREVTEMP VARCHAR(MAX)
SET @TEMP=@STR
SET @PREVTEMP=''
WHILE @TEMP IS NOT NULL
BEGIN
--FETCH ONLY THE FIRST PARTITION WITH DELIMITER AS ';'
SET @PREVTEMP=SUBSTRING(@TEMP,1,NULLIF(CHARINDEX(';',@TEMP),0)-1)
--INSERT IF NOT NULL. FOR LAST PARTITION @PREVTEMP WILL BE NULL COZ OF NULLIF
IF @PREVTEMP IS NOT NULL
INSERT @TMPTABLE
SELECT LTRIM(RTRIM(@PREVTEMP))
ELSE
INSERT @TMPTABLE
SELECT LTRIM(RTRIM(@TEMP))
--REPLACE ALREADY INSERTED PARTITION WITH EMPTY STRING
SELECT @TEMP=REPLACE(@TEMP,SUBSTRING(@TEMP,1,NULLIF(CHARINDEX(';',@TEMP),0)),'')
END

RETURN
END
GO

SELECT RID,NAME FROM YOURTABLE
CROSS APPLY TEST(TEAM)

--------------------
Rock n Roll with SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 11:27:50

quote:
Originally posted by keentolearn

Hi Peso, I have tried using the dbo.fnParseList but it didnt work quite right e.g.
RecordID 1000962 has three team members in one cell which after running the script should return:
1000962 Parker, Tom
1000962 Brown, Tim
1000962 Smith, Jon

instead it returns:
1000962 Parker, Tom;Brown, Tim;Smith, Jon
1000962 Parker, Tom;Brown, Tim;Smith, Jon
1000962 Parker, Tom;Brown, Tim;Smith, Jon

Looks ok to me


declare @Table1 table
(
	RecordID	int,
	Team		varchar(50)
)
insert into @Table1
select	1000 , 'Smith, John; Brown, Matt'		union all
select	1010 , 'Halls, G; Jones, Tom; Michael, Jason'	union all
select	1015 , 'Brown, Olly'				union all
select	1000962, 'Parker, Tom;Brown, Tim;Smith, Jon'

SELECT 	mt.RecordID, ltrim(f.Data) as Member
FROM 	@Table1 AS mt
	CROSS APPLY dbo.fnParseList(';', mt.Team) AS f

RecordID    Member
----------- ----------------
1000        Smith, John
1000        Brown, Matt
1010        Halls, G
1010        Jones, Tom
1010        Michael, Jason
1000962     Parker, Tom
1000962     Brown, Tim
1000962     Smith, Jon

(8 row(s) affected)

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-17 : 11:44:58

quote:

Looks ok to me

declare @Table1 table
(
RecordID int,
Team varchar(50)
)
insert into @Table1
select 1000 , 'Smith, John; Brown, Matt' union all
select 1010 , 'Halls, G; Jones, Tom; Michael, Jason' union all
select 1015 , 'Brown, Olly' union all
select 1000962, 'Parker, Tom;Brown, Tim;Smith, Jon'

SELECT mt.RecordID, ltrim(f.Data) as Member
FROM @Table1 AS mt
CROSS APPLY dbo.fnParseList(';', mt.Team) AS f

RecordID Member
----------- ----------------
1000 Smith, John
1000 Brown, Matt
1010 Halls, G
1010 Jones, Tom
1010 Michael, Jason
1000962 Parker, Tom
1000962 Brown, Tim
1000962 Smith, Jon

(8 row(s) affected)

It is missing one row. Is this intentional?

--------------------
Rock n Roll with SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 11:57:07

quote:
It is missing one row. Is this intentional?

I used the wrong version of fnParseList. Refer to the thread for detail http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 12:04:08

this is the updated of Peter's fnParseList


CREATE FUNCTION dbo.fnParseList
(
	@Delimiter CHAR,
	@text VARCHAR(MAX)
)
RETURNS @Result TABLE (RowID smallint IDENTITY(1, 1) PRIMARY KEY, Data varchar(8000))
AS

BEGIN
	DECLARE	@NextPos int,
		@LastPos int

	SELECT	@NextPos = CHARINDEX(@Delimiter, @text, 1),
		@LastPos = 0

	WHILE @NextPos > 0
		BEGIN
			INSERT	@Result
				(
					Data
				)
			SELECT	SUBSTRING(@text, @LastPos + 1, @NextPos - @LastPos - 1)

			SELECT	@LastPos = @NextPos,
				@NextPos = CHARINDEX(@Delimiter, @text, @NextPos + 1)
		END

-- 	IF SCOPE_IDENTITY() > 0
	IF @NextPos <= @LastPos
		INSERT	@Result
			(
				Data
			)
		SELECT	SUBSTRING(@text, @LastPos + 1, DATALENGTH(@text) - @LastPos)

	RETURN
END

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-17 : 12:18:34

Yes it is perfect. Thanks for that.

--------------------
Rock n Roll with SQL

keentolearn
Starting Member

21 Posts

Posted - 2009-07-17 : 14:40:37

i can not connect to the server at the moment. Will try Monday morning but many thanks in advance. I am sure It will work! Have a nice weekend.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-18 : 19:47:27

quote:
Originally posted by DonAtWork

sounds like a job for PIVOT

How so?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 17:32:04

Peter,

I have to ask... why are you still using a While Loop to do splits?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-20 : 02:08:56

You mean using a tally numbers table?

Well, I read the "double barreled, double carborated" thread on SSC and sure the CLR routine is the fastest. I still suggest the WHILE LOOP since it is easy to implement and easy enough for users to understand. Most of my students doesn't understand the tally numbers approach (yet) and that is a key factor for me and them.
The code has to be undestandable and maintanable.

And the WHILE LOOP is still fast enough to compete with the tally numbers approach.

We had a similar "double barreled, double carborated" thread here some time ago
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2#305425

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 02:15:55

I guess for practicability. I would use a WHILE loop approach for the most simple reason. I can't use CLR on SQL 2000. And i don't like the idea of maintaining 2 different version of function

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

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-20 : 03:16:07

quote:
Originally posted by Peso

You mean using a tally numbers table?

Well, I read the "double barreled, double carborated" thread on SSC and sure the CLR routine is the fastest. I still suggest the WHILE LOOP since it is easy to implement and easy enough for users to understand. Most of my students doesn't understand the tally numbers approach (yet) and that is a key factor for me and them.
The code has to be undestandable and maintanable.

And the WHILE LOOP is still fast enough to compete with the tally numbers approach.

We had a similar "double barreled, double carborated" thread here some time ago
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648&whichpage=2#305425

Ummmm... not sure how to say this without starting a huge fight with all of the folks on that thread, but the data in the tests sucked. The data made certain methods look good. I posted alternate data a couple of times and the euphoria on that post made people blind. So far as the CLR method goes, I have a Tally table method that rivals even that. You'll be one of the first to read about it if I can get Tony Davis to accept you as my technical reviewer. ;-)

So far as a While Loop being "good enough", heh... you know me. It's a form of RBAR that I'll never be happy with.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-20 : 03:17:39

quote:
Originally posted by khtan

I guess for practicability. I would use a WHILE loop approach for the most simple reason. I can't use CLR on SQL 2000. And i don't like the idea of maintaining 2 different version of function

Shoot... I wouldn't use the CLR solution even if SQL Server 2000 could use CLR's. I don't like the idea of having to use 2 different languages to do one job.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

keentolearn
Starting Member

21 Posts

Posted - 2009-07-23 : 06:01:50

Sorry, but it is still not doing what I need. I am running the following Select statement:

SELECT p.RecordID, p.person FROM People AS p
CROSS APPLY dbo.fnParseList(';', p.person) AS f
where p.person is not null
order by recordID asc;

it returns the correct number of recordIDs, i.e. if a record has two person names separated by ;, it returns two rows for that record id however, both person names are returned each time with the ;..

RecordID - Person
100092 - Block, Jo;Smith, Andrew
100092 - Block, Jo;Smith, Andrew

where it should be:
100092 - Block, Jo
100092 - Smith, Andrew

I have created Peter's updated fnParseList. Not sure what I am doing wrong here?

Thanks.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources