Separate List into Rows

Question

Separate List into Rows

dsmith402

SSC Enthusiast

Points: 128
More actions
December 16, 2016 at 4:38 pm

#401221

Looking for a non-user function that will allow me to separate items within a field, only separated by a comma, into new rows.
I'm not too familiar with SUBSTRING and CHARINDEX; however, the examples I found all seem to utilize these.
Here's what I've attempted, but not seeing the required results.
,SUBSTRING(TechnologyTeams, CHARINDEX(',', TechnologyTeams) , CHARINDEX(',', TechnologyTeams, 0) ) AS [TechTeam]
My field looks like this:
Business Intelligence, Custom Applications - Roll (All apps except Loss Mit), Data Management, Web/Middleware
Query Results:
, Custom Applications
So I got one, and only one team, from the string but still missing the others. Also wouldn't need the leading comma in the current query results.
Any suggestions?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110651 More actions · Answer 1

dsmith402 (12/16/2016)
Looking for a non-user function that will allow me to separate items within a field, only separated by a comma, into new rows.
I'm not too familiar with SUBSTRING and CHARINDEX; however, the examples I found all seem to utilize these.
Here's what I've attempted, but not seeing the required results.
,SUBSTRING(TechnologyTeams, CHARINDEX(',', TechnologyTeams) , CHARINDEX(',', TechnologyTeams, 0) ) AS [TechTeam]
My field looks like this:
Business Intelligence, Custom Applications - Roll (All apps except Loss Mit), Data Management, Web/Middleware
Query Results:
, Custom Applications
So I got one, and only one team, from the string but still missing the others. Also wouldn't need the leading comma in the current query results.
Any suggestions?

You're going to want to learn about a string splitting function found here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D

Here's how it could be used for your scenario:

DECLARE @TEST AS TABLE (

ID int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,

FIELD varchar(200)

);

INSERT INTO @TEST(FIELD)

SELECT 'Business Intelligence, Custom Applications - Roll (All apps except Loss Mit), Data Management, Web/Middleware';

SELECT S.ItemNumber, LTRIM(S.Item) AS FIELD_VALUE

FROM @TEST AS T

CROSS APPLY dbo.DelimitedSplit8K(T.FIELD, ',') AS S;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Steve hit the nail on the head. Tell me, where does the "Looking for a non-user function" requirement come from? It's the right tool for the job.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

dsmith402 SSC Enthusiast Points: 128 More actions · Answer 3

Thanks for the info.

The reason I need to avoid functions is because I need this to execute in Excel. Based on my experience Excel doesn't always like complex coding. I've run into issues using recursive code not executing via Excel.

My need is only temporary, but that temporary use may last for up to 1 year.

sgmunson SSC Guru Points: 110651 More actions · Answer 4

dsmith402 (12/17/2016)
Thanks for the info.
The reason I need to avoid functions is because I need this to execute in Excel. Based on my experience Excel doesn't always like complex coding. I've run into issues using recursive code not executing via Excel.
My need is only temporary, but that temporary use may last for up to 1 year.

Umm... This is a SQL Server forum, not an Excel one. You're probably in need of a VBA function instead. Why did you post this need in a SQL Server forum?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110651 More actions · Answer 5

dsmith402 (12/17/2016)
Thanks for the info.
The reason I need to avoid functions is because I need this to execute in Excel. Based on my experience Excel doesn't always like complex coding. I've run into issues using recursive code not executing via Excel.
My need is only temporary, but that temporary use may last for up to 1 year.

Maybe you're trying to execute a query using VBA within Excel? If that's the case, then just hijack the code within the function and make it part of your query. It's not that big a challenge. As that function contains a number of CTEs (common table expressions), you can just turn the last SELECT into another CTE, and then use the code I provided above to CROSS APPLY the CTE that you create from that final SELECT. Give it a try, and if you can't quite figure it out, post what you tried and we can help you get over that hump.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)