Let's say I have a table named Orders having the following columns:
ID - Int Identity(1,1)
CustomerNo - varchar(10)
OrderNo - varchar(10)
OrderDate - Date
ItemNo - varchar(10)
QtySold - float
DeptCode - varchar(3000)
The column DeptCode is a comma-separated list of department codes, e.g., '101, 102, 103'. Sometimes this column has only one department code and sometimes it is blank, i.e., no department code at all.
I would like to transform this table to a new table by splitting the DeptCode column into as many rows as needed:
For each row whose DeptCode is blank, create one row in the new table with a blank DeptCode.
For each row whose DeptCode contains only one department code, create one row in the new table with that department code.
For each row whose DeptCode contains multiple department codes, create multiple rows in the new table having those department codes.
Assume the new table is named NewOrders and has the following structure:
NewID - Int Identity(1,1)
CustomerNo - varchar(10)
OrderNo - varchar(10)
OrderDate - Date
ItemNo - varchar(10)
QtySold - float
DeptCode - varchar(5)
So, NewID is unique in NewOrders, but the other columns CustomerNo, OrderNo, OrderDate, ItemNo, QtySold will duplicate since they will come from the same row that is being split.
How do I do this? Can this be done without a cursor?
create table #temp
(
id int ,
comma_string varchar(100)
)
insert into #temp select 1,'abc,def,ghi'
insert into #temp select 2,'xx,yy,zz,kk'
select 'data',* from #temp
......SQL Server 2016 and above ....
SELECT 'SQL Output',id,value
FROM #temp
CROSS APPLY STRING_SPLIT(comma_string, ',');
A better idea... instead of sifting through all that manure to figure out what the horse was thinking, ask the horse, instead. Here's the original link for the DelimitedSplit8K function along with charts and graphs to compare the performance and functionality. I can personally vouch for the author.
Don't build your new table this way - what you really need is 2 tables. The first table has all the same columns minus the DeptCode. The new table has the PK from this table and the DeptCode - just those 2 columns set as the PK on this new table.
In fact - instead of creating 2 new tables, just create the one new table - and use the string splitter utility already outlined to provide the data for the new table. Now you can create a view from the original table - joined to the new table that returns the data in the format expected.