Creating a row for each value in a comma-separated string

Question

I'm currently trying to create a View from a Table that does the following:

Table A
╔═════════════╦═══════════════╗ 
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║    FOO,BAR    ║
║  PN0004-01  ║  BAR,FOO,FOB  ║
╚═════════════╩═══════════════╝

To create

View B
╔═════════════╦═══════════════╗ 
║ PART NUMBER ║   FILE NAME   ║
╠═════════════╬═══════════════╣
║  PN0001-01  ║      FOO      ║
║  PN0002-01  ║      BAR      ║
║  PN0003-01  ║      FOO      ║
║  PN0003-01  ║      BAR      ║
║  PN0004-01  ║      BAR      ║
║  PN0004-01  ║      FOO      ║
║  PN0004-01  ║      FOB      ║
╚═════════════╩═══════════════╝

So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.

Answer 1

First, create a string splitting function:

CREATE FUNCTION dbo.SplitString
(
    @List      nvarchar(max),
    @Delimiter nvarchar(10)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    WITH x(x) AS 
    (
      SELECT 1 UNION ALL SELECT x+1 FROM x 
      WHERE x < (LEN(@List))
    )
    SELECT Item = SUBSTRING(@List, x, 
      CHARINDEX(@Delimiter, @List + @Delimiter, x) - x)
    FROM x WHERE x <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, x, 1) = @Delimiter  );
GO

Then you can simply cross apply your source with the function:

DECLARE @x TABLE(PartNumber nvarchar(255), FileName nvarchar(max));

INSERT @x(PartNumber, FileName) VALUES
('PN0001-01','FOO'),
('PN0002-01','BAR'),
('PN0003-01','FOO,BAR'),
('PN0004-01','BAR,FOO,FOB');

-- INSERT dbo.ViewName(col1,col2)
SELECT x.PartNumber, f.Item
FROM @x AS x
CROSS APPLY dbo.SplitString(x.FileName, N',') AS f;

In SQL Server 2016, you can use the native STRING_SPLIT() function.

Answer 2

In SQL Server 2016 (although not the version requested by the original poster), you can use STRING_SPLIT, which does what @Aaron Bertrand proposed in his answer::

SELECT 
    part_number, value AS file_name
FROM 
    parts CROSS APPLY STRING_SPLIT(file_name, ',') 
ORDER BY 
    part_number, file_name ;

... assuming this is the original data

ALTER DATABASE my_database 
    SET COMPATIBILITY_LEVEL  = 130 ;

CREATE TABLE parts
(
    part_number varchar(100) PRIMARY KEY,
    file_name varchar(100)
) ;

INSERT INTO parts 
    (part_number, file_name)
VALUES 
    ('PN0001-01', 'FOO'),
    ('PN0002-01', 'BAR'),
    ('PN0003-01', 'FOO,BAR'),
    ('PN0004-01', 'BAR,FOO,FOB') ;

That would be the result:

part_number   file_name
PN0001-01     FOO
PN0002-01     BAR
PN0003-01     BAR
PN0003-01     FOO
PN0004-01     BAR
PN0004-01     FOB
PN0004-01     FOO