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

Goal:

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!

Problem:

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;