Data format in table of interest:
id (Foreign Key), field_id (numbers fields within each id, from 0), field (primary identifier of type of data stored in field_data), subfield (secondary identifier), field_data
I want to pull field_data into different columns based on values of field/subfield then output them to .csv or some other Stata compatible format. I had originally tried doing this with Python with the data in its original XML format but 20 hours in, decided SQL would work better. (Newbie problems). Thanks to this topic, I am nearly successful!
However, more than occasionally, an id will have many field_data values satisfying the same conditions. My query only outputs one of these. For example, a field = 650 for an observation with id 4 will have two subjects associated with it: Biology and Agriculture but my query only returns Biology in the subjects field. The only distinguishing tag for the two subjects is the field_id (field_id = 12 for Biology, for example and = 13 for Agriculture). I cannot simply add a field_id qualifier though because within another record/id/row, these field numbers may or may not still correspond to subject depending on how many field types come before. Additionally, the subject column is not the only column where I will run into this issue.
I suspect this has something to do with using the MAX function in the presented solution. However, I am unsure what else to use. Ideally, I could also get each additional subject into its own column too (subject1, subject2, etc).
For reference, I have been using SQLite.
Thank you for your time!
Script: (adjusted from solution in provided link)
SELECT id, MAX(CASE field WHEN '100' THEN field_data END) AS author, MAX(CASE field WHEN '791' THEN field_data END) AS degree, MAX(CASE field WHEN '792' THEN field_data END) AS year_awarded, MAX(CASE field WHEN '710' THEN field_data END) AS school, MAX(CASE field WHEN '590' THEN field_data END) AS schoolcode, MAX(CASE field WHEN '650' THEN field_data END) AS subjects, etc. ... FROM MARC_FIELDS WHERE field IN ('100', '791', '792', '710', '590', '650', etc., ...) GROUP BY id;