2

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.

  • 1
    What database platform are you using? There are different solutions depending on if you're using Microsoft SQL Server, MySQL, Oracle, etc. – AMtwo Feb 6 '17 at 22:08
  • SQL Server 2014 – ErickM Feb 6 '17 at 22:18
6

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.

| improve this answer | |
1

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.