Converting a Hash Map to a Table

Several times I have run into a conversion where I have needed to take a table that stores key value pairs into a table that lists all values for a given record.

The table look something like this:

Item_IDFieldValue
3ITEM CLASSIFICATIONHOT DOG
3ITEM DESCRIPTIONA TASTY CHICAGO STYLE DOG
3UNIT OF MEASURELENGTH
3HAS BUNY
4ITEM CLASSIFICATIONCORNDOG
4DIPPING SAUCEMUSTARD
4HAS BUNN
5ITEM CLASSIFICATIONHAMBURGER
5ITEM DESCRIPTIONA HAMBURGER WITH NO CHEESE
5UNIT OF MEASUREPOUND
5HAS BUNY

What I really wanted was something like this:

Item_IDItem ClassificationItem DescriptionUnit Of MeasureHas BunDipping Sauce
3HOT DOGA TASTY CHICAGO STYLE DOGLENGTHY
4CORNDOG NMUSTARD
5HAMBURGERA HAMBURGER WITH NO CHEESEPOUNDY

This is kind of like a PIVOT function, but since there can be a varied number of distinct records in the FIELD column, a pivot won’t always work.

What we can do is create a dynamic sql statement that will scan the distinct FIELD column and create a table for us based on the elements in the column.

 

0 Comments

Leave a Reply

Your email address will not be published.

Please type the characters of this captcha image in the input box

Please type the characters of this captcha image in the input box