SQLTeam.com | Weblogs | Forums

Need help splitting a coumn into rows

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:

  1. For each row whose DeptCode is blank, create one row in the new table with a blank DeptCode.
  2. For each row whose DeptCode contains only one department code, create one row in the new table with that department code.
  3. 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?

Thanks in advance for your much appreciated help!

hi

this is just sample data and showing the way

please click here for sample data script
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, ',');

image

1 Like

hi

different ways of doing it ... sql 2008

XML approach
Recursive Approach
and more ...

Please see below link ...

1 Like

Use the DelimitedSplit8K method from the site above. All the others suck so bad they have their own gravitational field..

2 Likes

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. :smiley:

\https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-“csv-splitter”-function

2 Likes

I will go one better :wink:

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.

1 Like
  1. Departmentcode is length 5, ain't gonna work with damppe data you provided.
  2. A recommended approach would be to
    Create a separate table of Departments then add a departmentid column in orders.
  3. Or an interesection table departmentOrders

Depends.

In what scenario would multiple departments share the same order?

Thank you so much! I'll use the DelimitedSplit8K function as it seems to be the fastest performing option.