SQL Server - Create cartesian product from comma delimited column

Question

I need to turn

Key     Location
123456  UK,France,Italy

into

Key    Location
123456 UK
123456 France
123456 Italy

Any ideas? Thanks

SQl2018. I can split the location colum with split_string delimited by the comma, but I cant find a way of then joining the result back to the original key — Paul, Jan 26 '22 at 14:23

score 2 · Answer 1 · answered Jan 26 '22 at 14:19

2

Typically, assuming SQL Server 2016 or better, this is:

SELECT t.[Key], Location = s.value
  FROM dbo.TableName AS t
  CROSS APPLY STRING_SPLIT(t.Location, N',') AS s;

Example db<>fiddle

If you're on < 2016 (or >= 2016 but < 130 compatibility level), you'll need to create a separate inline TVF to handle this (example forthcoming if necessary).

answered Jan 26 '22 at 14:19

Aaron Bertrand

272,866
37
466
490

awsome stiff, I'll give that a go and come back to you.. – Paul Jan 26 '22 at 14:29
or even stuff!! – Paul Jan 26 '22 at 14:29
Yes, super, I'' go through it and try and understand it! Thanks Aaron – Paul Jan 26 '22 at 14:32

SQL Server - Create cartesian product from comma delimited column

1 Answers1