sql.SplitColumnValueIntoMultipleRows

PHOTO EMBED

Mon Mar 14 2022 18:35:53 GMT+0000 (UTC)

Saved by @rick_m #sql

IF object_id('tempdb..#table') IS NOT NULL
BEGIN
   DROP TABLE #table
END

Create Table #table(
	[State] varchar(500),
	[City] varchar(500)
);


Insert into #table ([State], [City]) values ('OH', 'Toledo,Columbus');
Insert into #table ([State], [City]) values ('TN', 'Nashville,Memphis');



SELECT A.[State]
	,Split.a.value('.', 'VARCHAR(100)') AS String
FROM (
	SELECT [State]
		,CAST('<M>' + REPLACE([City], ',', '</M><M>') + '</M>' AS XML) AS String
	FROM #table
	) AS A
CROSS APPLY String.nodes('/M') AS Split(a);
content_copyCOPY