1

I've tried to find a solution that does this...I need to split a string that is in a column, to produce one entry per value, retaining the existing column values.

Example:

CREATE TABLE #SplitMeUp(
SomeInt INT NOT NULL
, SomeName VARCHAR(10) NOT NULL
, LongList VARCHAR(100) NOT NULL
)

INSERT INTO #SplitMeUp VALUES (1,'First', 'A,B,C,D')
INSERT INTO #SplitMeUp VALUES (2,'Second', 'B,C,D,E,F,G')
INSERT INTO #SplitMeUp VALUES (3,'Third', 'A')
INSERT INTO #SplitMeUp VALUES (4,'Fourth', 'A,B,C,D,E,F,G')

SELECT * FROM #SplitMeUp

1   First   A,B,C,D
2   Second  B,C,D,E,F,G
3   Third   A
4   Fourth  A,B,C,D,E,F,G

I am trying to achieve the following, but I can't use a function as I don't have permissions, on 2012 so can't use STRING_SPLIT and the solutions I have found just split the actual string, not retain the other values.

1   First   A
1   First   B
1   First   C
1   First   D
2   Second  B
2   Second  C
2   Second  D
2   Second  E
2   Second  F
2   Second  G
3   Third   A
4   Fourth  A
4   Fourth  B
4   Fourth  C
4   Fourth  D
4   Fourth  E
4   Fourth  F
4   Fourth  G
BlueChippy
  • 5,935
  • 16
  • 81
  • 131
  • 1
    Google: `SQL Server split`. – Gordon Linoff Jul 17 '19 at 12:03
  • 4
    https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows – Y.S Jul 17 '19 at 12:06
  • @GordonLinoff - There are just a few results returned for that! – BlueChippy Jul 17 '19 at 12:12
  • As a side note, in general this type of relationship is naturally implemented in Relational Algebra with a one-to-many relationship between the initial table and a variable number of values in a separate table. If distinct values can be associated with more than one tuple in the initial table a many-to-many relationship could be created to achieve this. – h0r53 Jul 17 '19 at 12:22
  • @BartHofland - Y.S. has already given that answer. – BlueChippy Jul 18 '19 at 16:07
  • I see. Strange. I didn't place that as a comment. So I removed it. I only flagged your question as a duplicate. Seems that the comment was somehow added automatically? – Bart Hofland Jul 18 '19 at 17:44

0 Answers0