Transpose part of SQL results to consolidate records

Question

I’m working on a project and it’s become a bit more complicated than I’m able to deal with. I have a piece of SQL that inner joins to several tables. One of these tables returns communication means for each individual (telephone number, radio call sign, pager, for example).

Once I’ve implemented this code, the SQL (quite rightly) returns multiple records for each individual, depending on which comms fields they’ve got filled out. So a search for myself would return:

Name  |  COMM_VALUE    | COMM_RID
Nick  |  07123 456789  |   5
Nick  |  0965          |   6
Nick  |  328493        |   7

Where as, I need:

Name  |  Mobile        |   Radio    |  Pager
Nick  |  07123 456789  |   0965     |  328493
Bob   |  etc etc etc

This is part of much larger table so my question is how do I add these columns to my existing SQL, I looked in to nested, subqueries, and unions but it’s an age since I did SQL and even my basic progress has been testing my capability.

I envisage something like the following but not sure of the keywords I need to achieve this:

Select
  PRP.FIRSTNAME + ‘ ‘ + PRP.SURNAME AS FULLNAME,
  RDP.POSTNAME,
  RRP.REMARKS,
  */ NEW COLUMNS */
      (SELECT COMM_VALUE FROM PERREC_COMMS WHERE COMM_RID = ‘6’) AS RADIO

The above (edited) code does yield results, but not quite as I anticipated:

Name  |  RADIO
Nick  |  0965
Nick  |  null
Nick  |  null

The above is then repeated for each person, with the other fields appearing correctly although duplicated on each row.

How can I consolidate these results to one row (exclude the ‘null’ results)?

Answer 1

You want to pivot the data. One method is conditional aggregation. For this purpose, I would recommend:

select name,
       group_concat(case when comm_rid = 5 then comm_value end) as what_5_means,
       group_concat(case when comm_rid = 6 then comm_value end) as radio,
       group_concat(case when comm_rid = 7 then comm_value end) as whateve_7_means
from . . .
group by name;

I have no idea from the question what from clause to use.

This uses group_concat() rather than max() just in case someone has multiple means of communications with the same type.