SQL: split one row into many (normalization)

Question

I am going to switch from a poorly created old database to a new database. The old database has table A with the Id and Commodities fields. The identifier is the main key and contains int and Commodities contains a comma-separated list.

Table a:

id   | commodities
1135 | fish,eggs,meat    
1127 | flour,oil  

In the new database, I want tableB to be in the form id, product, where each product is a separate item from a comma-delimited list in table A.

TableB:

id   | commodity
1135 | fish  
1135 | eggs   
1135 | meat  
1127 | flour  
1127 | oil    

I have a functionA function that, when given an id, list, and delimiter, returns a table with an id and item field. How can I use this function to turn two fields from table A to table B?

(: , , . , , !)

:

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

Answer 1

, :

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Answer 2

TSQL, . PRO CON :

SQL Server 2000

split. :

SELECT
    *
    FROM YourTable                               y
    INNER JOIN dbo.yourSplitFunction(@Parameter) s ON y.ID=s.Value

[ TSQL] ( SQL Server 2000 ), SQL Server, . , PRO CON .

Numbers Table , Numbers, 1 10000:

SELECT TOP 10000 IDENTITY(int,1,1) AS Number
    INTO Numbers
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number)

Numbers , :

CREATE FUNCTION inline_split_me (@SplitOn char(1),@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(@SplitOn + @param + ',', Number + 1,
                    charindex(@SplitOn, @SplitOn + @param + @SplitOn, Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(@SplitOn + @param + @SplitOn) - 1
            AND  substring(@SplitOn + @param + @SplitOn, Number, 1) = @SplitOn)

GO 

CSV :

select * from dbo.inline_split_me(';','1;22;333;4444;;') where LEN(Value)>0

:

Value
----------------------
1
22
333
4444

(4 row(s) affected)

, :

--set up tables:
create table TableA (id int, commodities varchar(8000))
INSERT TableA VALUES (1135,'fish,eggs,meat')
INSERT TableA VALUES (1127,'flour,oil')

Create table TableB (id int, commodities varchar(8000))

--populate TableB
INSERT TableB
    (id, commodities)
SELECT
    a.id,c.value
    FROM TableA    a
        CROSS APPLY dbo.inline_split_me(',',a.commodities) c

 --show tableB contents:
select * from TableB

:

id          commodities
----------- -------------
1135        fish
1135        eggs
1135        meat
1127        flour
1127        oil

(5 row(s) affected)

Conrad Frix , SQL Server 2000 CROSS APPLY

:

INSERT TableB
        (id, commodities)
    SELECT 
        a.id,NullIf(SubString(',' + a.commodities + ',' , number , CharIndex(',' , ',' + a.commodities + ',' , number) - number) , '')
        FROM TableA            a
            INNER JOIN Numbers n ON 1=1
        WHERE SubString(',' + a.commodities + ',' , number - 1, 1) = ',' 
        AND CharIndex(',' , ',' + a.commodities + ',' , number) - number > 0
        AND number <= Len(',' + a.commodities + ',') 

@Rup. ( split numbers), CROSS APPLY.

Answer 3

SQL, A b .

Answer 4

, , , . SQL...

Answer 5

SSIS Unpivot, .

Answer 6

create table Project (ProjectId int, Description varchar(50));
insert into Project values (1, 'Chase tail, change directions');
insert into Project values (2, 'ping-pong ball in clothes dryer');

create table ProjectResource (ProjectId int, ResourceId int, Name varchar(15));
insert into ProjectResource values (1, 1, 'Adam');
insert into ProjectResource values (1, 2, 'Kerry');
insert into ProjectResource values (1, 3, 'Tom');
insert into ProjectResource values (2, 4, 'David');
insert into ProjectResource values (2, 5, 'Jeff');

-- a bit of SQL magic involving XML and voila
SELECT *, 
   (SELECT Name + ' ' AS [text()] 
    FROM ProjectResource pr 
    WHERE pr.ProjectId = p.ProjectId
    FOR XML PATH ('')) AS ResourceList 
FROM Project p

:

ProjectId   Description                       ResourceList
1           Chase tail, change directions     Adam Kerry Tom 
2           ping-pong ball in clothes dryer   David Jeff