Breaking up a list into separate columns

Question

Breaking up a list into separate columns

scottgrund

Grasshopper

Points: 15
More actions
March 12, 2003 at 1:50 pm

#175329

I'm bringing into a table data from Lotus Notes. One of the columns is a list of names, sometimes empty, sometimes with one or more names separated by a comma. I need to create a new column for each name in the record. any ideas?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47797 More actions · Answer 1

quote:
... One of the columns is a list of names, sometimes empty, sometimes with one or more names separated by a comma. I need to create a new column for each name in the record ...

Am I reading this right in that you want multiple columns in the one record, not multiple records for each name?

If so, you would have to define your destination table with as many columns as names in the comma-seperated list. Then you would add an ActiveX Script to transform the one field of data into each destination column.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 03/12/2003 3:00:32 PM

--------------------
Colt 45 - the original point and click interface

scottgrund Grasshopper Points: 15 More actions · Answer 2

Actually, when I wrote the question, I was thinking multiple columns, however, I think what I need is each list member will become a separate record. I have two columns, Team and CallID. I need each member of the team to have their own record with their name in one column and the CallID in the next.

John Smith, Sue Jones, Bill Clinton DI3546

John Smith DI3456

Sue Jones DI3456

Bill Clinton DI3456

David Burrows SSC Guru Points: 65144 More actions · Answer 3

declare @CT int

set @CT = 1

while @CT > 0

begin

insert into #tableb select LEFT(Team,CHARINDEX(',',Team+',')-1) AS 'Name',CallID from #tablea where Team <> ''

update #tablea set Team = LTRIM(REPLACE(Team,LEFT(Team,CHARINDEX(',',Team+',')),'')) where Team <> ''

select @CT = count(*) from #tablea where Team <> ''

end

Far away is close at hand in the images of elsewhere.
Anon.

philcart SSC-Forever Points: 47797 More actions · Answer 4

So does the destination table just have those two fields?

If that's the case, it would probably be better if you import the data into a staging table. Then, using a stored procedure, break the comma delimited into seperate records to insert into your destination table.

If there's more than those two fields involved, you can use a second staging table to hold the seperated names and then insert into your destination table doing a join between the two staging tables.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

scottgrund Grasshopper Points: 15 More actions · Answer 5

quote:
declare @CT int
set @CT = 1
while @CT > 0
begin
insert into #tableb select LEFT(Team,CHARINDEX(',',Team+',')-1) AS 'Name',CallID from #tablea where Team <> ''
update #tablea set Team = LTRIM(REPLACE(Team,LEFT(Team,CHARINDEX(',',Team+',')),'')) where Team <> ''
select @CT = count(*) from #tablea where Team <> ''
end

Thanks for the code, but I have the situation that the Team column could be blank or with only one name in it. This code keeps running in a continual loop. I think I know how to fix that. Thank you again.

Scott Grund

Edited by - scottgrund on 03/13/2003 12:21:53 PM