Move the extra entry to a new row in SQL Server

Question

I have a table.

Country|  Continent|  City
----------------------------
USA      Americas   [{1,New York}]
Chile    Americas   [{2,Santiago}]
England  Europe     [{3,London},{4,Bristol}]

I want to move the extra entry to a new row in sql server. And the output needs to look like this.

----------------------------
Country|  Continent|  City
----------------------------
USA      Americas   [{1,New York}]
Chile    Americas   [{2,Santiago}]
England  Europe     [{3,London}]
England  Europe     [{4,Bristol}]

Have you had look at any string splitters? There are literally 1,000's of examples on Stack Overflow, and there's also a built in one on SQL Server 2016+. — Thom A, Jul 04 '18 at 13:44
Hi, welcome to SO. If you have written code for this that you can't get to work, then you have come to the right place. Just [edit your question](https://stackoverflow.com/posts/51175133/edit) and add the relevant parts of your code into it. You need to show your own efforts, because Stack Overflow is not a write-my-code-for-me service. Also please see **[ask]**. — Peter B, Jul 04 '18 at 13:44
@PeterB Sorry, I am pretty new to sql. I wish I could show my efforts here. But I hardly have any idea. — vv4, Jul 04 '18 at 13:52
May be duplicate of https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows — Code_Tech, Jul 04 '18 at 14:02

Sreenu131 · Answer 1 · 2018-07-04T14:21:42.443

Try this

;WITH CTE(Country,  Continent,  City)
AS
(
SELECT 'USA'    ,'Americas' ,'[{1,New York}]'   UNION ALL
SELECT 'Chile'  ,'Americas' ,'[{2,Santiago}]'   UNION ALL
SELECT 'England','Europe'   ,'[{3,London},{4,Bristol}]'
)
SELECT  Country,
        Continent,
        QUOTENAME(IIF(RIGHT(Split.a.value('.','nvarchar(1000)'),1)<>'}',Split.a.value('.','nvarchar(1000)')+'}' ,Split.a.value('.','nvarchar(1000)')))
AS City
FROM
(
SELECT Country,Continent,CAST('<S>'+ REPLACE(REPLACE(REPLACE(City,'[',''),']',''),'},','</S><S>' )+'</S>' AS XML) AS City
FROM CTE
)AS A
CROSS APPLY City.nodes('S') AS Split(a)

Demo Result : http://rextester.com/AWWN39720

Move the extra entry to a new row in SQL Server

1 Answers1