Single column into multiple columns with values based on delimiters

Question

Single column into multiple columns with values based on delimiters

aaronsmity

SSC Journeyman

Points: 92
More actions
February 2, 2017 at 9:39 am

#326546

Hi I am trying to figure out a way to parse a very long column and display it in a more user friendly manner so I can report off of it. As an example a single row in a column is setup in this way:
Type = Dog, Color = Brown, Color = White, Size = Large
I would like to use the = sign as a delimiter so anything before it would be the column name and anything after it up to the comma would be the row value. As you can see there are names that have more than one value also (i.e. color is both brown and white) which also causes an issue.
So the above data would be in parsed into 3 columns (Type, Color and Size)
with their respective values (Dog, White Brown, Large)
Is this possible? Thanks for helping, not sure why the developer setup the data in a way that requires this much parsing to make it more reportable. Thanks.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Use a splitter function to split at the commas, then use simple LEFT and RIGHT to separate the ensuing column into two columns: attribute and value. You'll then need to do some sort of dynamic pivot to transform the attributes to column headers.

John

pietlinden SSC Guru Points: 63616 More actions · Answer 2

First step is to give whoever did this a beating. Databases like this are pretty much useless.

Here's most of a solution:
link to DelimitedSplit8K

DECLARE @MyString VARCHAR(100) = 'Type = Dog, Color = Brown, Color = White, Size = Large';

SELECT ds.ItemNumber
, ds.Item
, CHARINDEX('=',ds.Item,1) AS EqPos
, LTRIM(RTRIM(LEFT(ds.Item,CHARINDEX('=',ds.Item,1)-1))) AS LeftSide
, RIGHT(ds.Item,LEN(ds.Item) - CHARINDEX('=',ds.Item,1)) AS RightSide
FROM Sandbox.dbo.DelimitedSplit8K(@MyString,',') ds;

aaronsmity SSC Journeyman Points: 92 More actions · Answer 3

Thanks for the quick replies... it looks like your code requires the DelimitedSplit8K function which looks like it is available here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

My question is, I have limited privileges to the database... is creating a "local" function possible so I can use it? Forgive my ignorance I am not familiar with the ins and outs of sql. Thanks for all your help.

EDIT: agreed on the database structure haha.. it was created by a third party as a backend for their software which has terrible reporting features so I thought I might be able to do better on the backend... so far its been a pain but hoping I can make some headway. thanks!

pietlinden SSC Guru Points: 63616 More actions · Answer 4

If you're not allowed to create the function, ask someone with permissions to do it. If they want their answers, they're going to have to comply. =) The function can be put in another "utility" database, just to keep the database clean... you just have to preface the function name with DatabaseName.SchemaName when you call it.

Yes, that's the article... (sorry, missed that part!)

aaronsmity SSC Journeyman Points: 92 More actions · Answer 5

aaronsmity

SSC Journeyman

Points: 92

February 2, 2017 at 1:07 pm

#1926452

Thank you for your help