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.

• anon: What database platform are you using? There are different solutions depending on if you're using Microsoft SQL Server, MySQL, Oracle, etc.
• ErickM: SQL Server 2014

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