st: reshaping key-value pair data

["Dimitriy V. Masterov" <dvmaster@gmail.com>]

I have some data in an awkward key-value pair format:

item key value
1 color blue
1 color red
1 size XL
2 color orange
2 size S

It is possible to reshape this data into something like this:

item color1 color2 size
1 blue red XL
2 orange S

The order for the values should be alphabetical,so blue before red.

I tried the following:

gen color = value if key=="color"
gen size = value if key=="size"

sort item key value
collapse (firstnm) color1=color (lastnm) color2=color (firstnm) size, by(item)

This mostly works, but it won't work for more than 2 values per key
and orange appears twice for item 2.

DVM
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/