I am in middle of upgrading from a poorly designed legacy database to a new database. In the old database there is tableA with fields Id and Commodities. Id is the primary key and contains an int and Commodities contains a comma delimited list.
TableA:
id | commodities
1135 | fish,eggs,meat
1127 | flour,oil
In the new database, I want tableB to be in the form id, commodity where each commodity is a single item from the comma delimited list in tableA.
TableB:
id | commodity
1135 | fish
1135 | eggs
1135 | meat
1127 | flour
1127 | oil
I have a function, functionA, that when given an id, a list, and a delimiter, returns a table with an id and item field. How can I use this function to turn the two fields from tableA into tableB?
(Note: I had trouble figuring out what to title this question. Please feel free to edit the title to make it more accurately reflect the question!)
Here is the function code:
ALTER FUNCTION dbo.functionA
(
@id int,
@List VARCHAR(6000),
@Delim varchar(5)
)
RETURNS
@ParsedList TABLE
(
id int,
item VARCHAR(6000)
)
AS
BEGIN
DECLARE @item VARCHAR(6000), @Pos INT
SET @List = LTRIM(RTRIM(@List))+ @Delim
SET @Pos = CHARINDEX(@Delim, @List, 1)
WHILE @Pos > 0
BEGIN
SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
IF @item <> ''
BEGIN
INSERT INTO @ParsedList (id, item)
VALUES (@id, CAST(@item AS VARCHAR(6000)))
END
SET @List = RIGHT(@List, LEN(@List) - @Pos)
SET @Pos = CHARINDEX(@Delim, @List, 1)
END
RETURN
END
Here's the link I posted as a comment:
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows