How to Parse a Database Field With Multiple Values Into Separate Columns

Dear Experts,

I have a field in one of my SQL Server tables called 'Categories'. It contains a number of associated items each with a value and separated by a ';'. For example the filed might contain something like the following.

Location=CONTROL BUILDING;Loop=164;Tag=FL-11;Priority=2;Count=0

When I query my report I get all of the above listed under a single column caled 'Categories'.

I would like to be able to list each item in the 'Categories' field in its own column. Also, depending upon the report type I would not want to list all the items but only those that might be relevant.

Thanks, Gary
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
I would create a new table (Categories_expanded or whatever name you want to give it) and normalize the data.

Meaning the new table will have columns for Loop, Tag, Priority, Count etc. and you select the data from the Categories table, explode it using the language of your choice and insert it into the appropriate column.

After that you can get easily get the reports you want.

Basically you'll have to split this string with a user defined function like described in Split Function MS SQL
Use this function with ";" as delimiter for the individual parameters and use it again on the result with the "=" to get fieldname and value.
These can be stored in a normalised table as Dan describes or in a "general table" with just "FieldName" and "Value"
When normalized you would have to identify the different combinations and you can put it in columns in different (?) tables giving one line per Categorie) or use the universal "general table" with one line per field/value combination.
Either way will involve some function programming for generating the table(s)....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ElisysAuthor Commented:
Using SSRS 2014, I found that it already included the SPLIT function under Common Functions -> Text. Hence something like the following would produce prliminary results.

=Split(Fields!Categories.Value, ";")(2)

This gave a single column list to display only the second item in the semi-colon seperated list. With a little more work I should be able toextract the additional columns and display each in turn.

Thanks for suggesting the SPLIT function although in this case I didn't need a user defined function since SSRS already had it there.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.