A while back I posted a simple way to aggregate strings in SQL Server, but what if your task is opposite – you have a character-separated string values in your fields and you need to slice them into separate rows? Consider following scenario, you have a table:
CREATE TABLE #Employees (Country varchar(50), FirstNames varchar(50)) INSERT INTO #Employees(Country,FirstNames) VALUES ('UK','Anne,Michael,Robert,Steven') INSERT INTO #Employees(Country,FirstNames) VALUES ('USA','Andrew,Janet,Laura,Margaret,Nancy') SELECT Country, FirstNames FROM #Employees
That looks like this
Country FirstNames UK Anne,Michael,Robert,Steven USA Andrew,Janet,Laura,Margaret,Nancy
and now you need to split FirstNames
so that every FirstName
is displayed on separate row. Continue reading →