sql server programming: Separate data in one field to individual column -- sql server programming

Separate data in one field to individual column

Sql Fren
7/11/2005 11:36:02 PM

sql server programming: I have a database records as below:

ItemId WhereUsed
A123 A124;B156;C679;D789
A133 A678
A143 H768;A789
A153 F899;S687

How can I remove the semicolon (;) and separate these values into different
columns as below:

ItemId WhereUsed1 WhereUsed2 WhereUsed3 WhereUsed4
A123 A124 B156 C679
D789
A133 A678
A143 H768 A789
A153 F899 S687

Please note that this WhereUsed column can be unlimited. So I won't be able
to know how many columns I need to cater for this.

Any solutions?

RE: Separate data in one field to individual column

John Bell
7/12/2005 12:15:02 AM

Hi

You may want to do this on the client as it should be the quickest method.
Alternatively you could check out
http://www.sommarskog.se/arrays-in-sql.html.

John

[quoted text, click to view]

Re: Separate data in one field to individual column

David Portas
7/13/2005 8:03:13 AM

Both designs look wrong to me. They look more like reports than tables.
Why not just two columns?

CREATE TABLE foo (item_id CHAR(4) NOT NULL REFERENCES Items (item_id),
location_id CHAR(4) NOT NULL REFERENCES Locations (location_id),
PRIMARY KEY (item_id, location_id))

--
David Portas
SQL Server MVP
--

Re: Separate data in one field to individual column

SJ
7/13/2005 4:30:34 PM

How about running (thru the table) the WhereUsed field thru a cursor and
getting the max WhereUsed## fields, ie, finding the record that requires
the maximum WhereUsed## fields. by using some smarts, maybe a string
function or your own loop.

once you know that, you can make a CREATE TABLE command and execute it.
(the original 2 fields and the extra WhereUsed##)

Throw in the original fields to the newly created table and roll thru the
cursor again. by using similar logic to the loop above you can create an
update statement for that record, as you loop thru.

sweet???
dun like a dinner

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.