SQL: Split two comma separated strings into separate indexed rows using SQL

Question

I have data stored such that my query returns the following:

EntryNum  FieldNames                FootnoteIDs
1         FieldA, FieldA, FieldB    F1, F3, F2
2         FieldA, FieldA, FieldB    F1, F4, F2
3         FieldB, FieldC, FieldD    F1, F12, F13

I'd like to return this as follows:

EntryNum  FieldName  FootnoteID
1         FieldA     F1
1         FieldA     F3
1         FieldB     F2
2         FieldA     F1
2         FieldA     F4
2         FieldB     F2
3         FieldB     F1
3         FieldC     F12
3         FieldD     F13

What would the SQL for this look like? Is it doable without using either a user-defined function or SPLIT_STRING?

The closest I found to a solution was here: Split comma separated string table row into separate rows using TSQL, but that doesn't have the dual splitting behavior I need. Further details a) there isn't a limit to the number of fields per Entry or FootnoteIDs per entry, and b) it's just the order of the FieldNames and FootnoteIDs that I need matched.

Looks like this is answered here, the technique should be able to give corresponding row-by-row results for as many columns as you need. https://stackoverflow.com/questions/19073500/sql-split-comma-separated-row — Mic, Aug 07 '17 at 22:23
I already looked at that answer and it doesn't work for this use case. It returns a single column full of all of the comma separated items, whereas I need the items from two different fields to be put into rows relationally. — Sebmono, Aug 08 '17 at 13:57
For SQL Server: consider he various user defined function approaches detailed here https://sqlperformance.com/2012/07/t-sql-queries/split-strings — Paul Maxwell, Aug 09 '17 at 01:25
The server I'm working on won't allow for the creation/use of user-defined functions, so that is unfortunately out of the question here. — Sebmono, Aug 15 '17 at 12:19

Paul Maxwell · Answer 1 · 2017-08-09T01:09:00.320

This answer provided when the question was tagged for MySQL.

Your expected result requires that there is a one-to-one relationship of fieldname to FootnoteID, so the logic to locate each part of one concatenated colunm is the same as that required for the second. Put another way: If your data has an equal number of parts in both the comma separated columns then a single method to split both can be used:

CREATE TABLE Table1
    (`EntryNum` int, `FieldNames` varchar(22), `FootnoteIDs` varchar(12))
;

INSERT INTO Table1
    (`EntryNum`, `FieldNames`, `FootnoteIDs`)
VALUES
    (1, 'FieldA, FieldA, FieldB', 'F1, F3, F2'),
    (2, 'FieldA, FieldA, FieldB', 'F1, F4, F2'),
    (3, 'FieldB, FieldC, FieldD', 'F1, F12, F13')
;

Query 1:

SELECT
      t.EntryNum
    , SUBSTRING_INDEX(SUBSTRING_INDEX(t.FieldNames, ',', n.n), ',', -1) FieldName
    , SUBSTRING_INDEX(SUBSTRING_INDEX(t.FootnoteIDs, ',', n.n), ',', -1) FootnoteID
FROM table1 t 
CROSS JOIN  (
   SELECT a.N + b.N * 10 + 1 n
   FROM  (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  ) n
WHERE n.n <= 1 + (LENGTH(t.FieldNames) - LENGTH(REPLACE(t.FieldNames, ',', '')))
order by t.EntryNum, n.n

See this SQL Fiddle

| EntryNum | FieldName | FootnoteID |
|----------|-----------|------------|
|        1 |    FieldA |         F1 |
|        1 |    FieldA |         F3 |
|        1 |    FieldB |         F2 |
|        2 |    FieldA |         F1 |
|        2 |    FieldA |         F4 |
|        2 |    FieldB |         F2 |
|        3 |    FieldB |         F1 |
|        3 |    FieldC |        F12 |
|        3 |    FieldD |        F13 |

Note the query above is a derivative of the answer at SQL split comma separated row and that answer refers to the benefits of a permanent "tally table" instead of the dynamic subquery (n) as seen above.

Apologies, I mis-tagged my question as being for MySQL, when in actuality I'm using SQL Server. The SUBSTRING_INDEX function does not exist in SQL Server, it seems. This looks like the right idea though! Is there an equivalent implementation for SQL Server? — Sebmono, Aug 08 '17 at 13:54
That was quite a mis-tag to make! (please double check in future). It can be achieved in SQL Server - but what version are you using (this can make a difference). nb: research "split string functions tsql". Starting with SQL Server 2016 there is an inbuilt function https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql — Paul Maxwell, Aug 09 '17 at 01:06
When I run the server version query it tells me that I'm using SQL Server 2016, but also doesn't recognize the split_string function. Does anyone know why that would be? I checked the compatibility level and it says it's 130. — Sebmono, Aug 15 '17 at 12:21
Is that a typo? The function is string_split not split_string. — Mic, Aug 20 '17 at 11:18

SQL: Split two comma separated strings into separate indexed rows using SQL

1 Answers1