SQL: Data is split into multiple rows

Question

The issue I ran into with SQL is that I tried to export data from one column that is linked to an ID into 3 other columns, however I ended up with lots of NULLS and multiple identical IDS as seen in the picture below.

I have a table with 4 columns, in these columns data is stored in a quite odd fashion. My table looks like this

entry_id| field_id |      value                 | value_ID |
|    480|       -5 |      string of text| 10    |
|    480|      -20 |      string of other text  | 10       | 
|    480|      -23 |      Yes                   | 10       |
|    480|      -22 |      No                    | 10       |

I've designed a query that turns the field ID into a new column with the attached value.

The query looks like this:

 SELECT 
 ticket.ticket_id,
 ticket.number,
 users.name,

 CASE WHEN val.field_id IN (5) THEN val.value end as "Issue Summary",
 CASE WHEN val.field_id IN (20) THEN val.value end as "Project Site",
 CASE WHEN val.field_id IN (23) THEN val.value end as "Action"

 FROM ost_ticket ticket

 LEFT JOIN ost_form_entry entry ON (ticket.ticket_id = entry.object_id)
 LEFT JOIN ost_form_entry_values val ON (entry.id = val.entry_id)
 LEFT JOIN ost_user users ON (ticket.user_id = users.id)
 LEFT JOIN ost_form_field fields ON (val.field_id = fields.id)

This query returns: https://i.sstatic.net/hqBv0.png (cant embed picture due to lack of reputation.)

As you can see I have multiple identical ticket ID' and lots of NULLS. What query should I use to have only one row per unique ticket_id

Answer 1

I am guessing that what you want is to get field values in one row:

SELECT 
 ticket.ticket_id,
 ticket.number,
 users.name,
 issue_sum.value as "Issue Summary",
 project_site.value as "Project Site",
 action.value as "Action"
 FROM ost_ticket ticket
 LEFT JOIN ost_form_entry entry ON (ticket.ticket_id = entry.object_id)
 LEFT JOIN ost_form_entry_values issue_sum 
     ON (entry.id = issue_sum.entry_id) and issue_sum.field_id = 5
 LEFT JOIN ost_form_entry_values project_site 
     ON (entry.id = project_site .entry_id) and project_site.field_id = 20 
 LEFT JOIN ost_form_entry_values action 
     ON (entry.id = action.entry_id) and action.field_id = 23
 LEFT JOIN ost_user users ON (ticket.user_id = users.id)
 LEFT JOIN ost_form_field fields ON (val.field_id = fields.id)

Given that you can have multiple entity records per ticket, only thing I can suggest is to use aggregation:

  SELECT 
     ticket.ticket_id,
     ticket.number,
     users.name,
     MAX(issue_sum.value) as "Issue Summary",
     MAX(project_site.value) as "Project Site",
     MAX(action.value) as "Action"
     FROM ost_ticket ticket
     LEFT JOIN ost_form_entry entry ON (ticket.ticket_id = entry.object_id)
     LEFT JOIN ost_form_entry_values issue_sum 
         ON (entry.id = issue_sum.entry_id) and issue_sum.field_id = 5
     LEFT JOIN ost_form_entry_values project_site 
         ON (entry.id = project_site .entry_id) and project_site.field_id = 20 
     LEFT JOIN ost_form_entry_values action 
         ON (entry.id = action.entry_id) and action.field_id = 23
     LEFT JOIN ost_user users ON (ticket.user_id = users.id)
     LEFT JOIN ost_form_field fields ON (val.field_id = fields.id)
GROUP BY  
     ticket.ticket_id,
     ticket.number,
     users.name;