simple mysql query design help Simple query design help. Below is my query:

SELECT tkt.ticket_id, tkt.`number`, val.field_id, FF.name,    val.value 
FROM os_TICKET tkt, os_form_entry_values val, os_form_field FF 
where tkt.ticket_id = val.entry_id 
and val.field_id = FF.id 


OUTPUT

ticket_id   number  field_id    name        value   
5       592593         4        SSN         11111111111
5       592593         87       title       Mr
5       592593         117      dob         05/01/2011
5       592593         3        mobile      0091981123232


I want to have the output like this:

ticket_id  Number    SSN          Title   dob            mobile
5          592593    1111111111   Mr.    05/012011  009198182838485


the ssn, title, dob and mobile are in another table (form_entry_value) and is linked with the ticket_id

Server version: 5.6.17 MySQL Community Server (GPL)
Update: I have change a bit the stored procedure to cope with null values.

Update: I have added fiddle

I have deduced your schema. I think that you schema is generated by:

create table  os_TICKET (
  ticket_id int not null primary key,
  number int not null
);

insert into os_TICKET values (5,592593);

create table os_form_entry_values (
  entry_id int not null,
  field_id int not null,
  value varchar(100)
);

insert into os_form_entry_values values
(5, 4, "111111"),
(5, 87, "Mr"),
(5, 117, "05/01/2011"),
(5, 3, "0091981123232");

create table os_form_field(
  id int not null primary key,
  name varchar(16)
);

insert into os_form_field values
(4, "SSN"),
(87, "title"),
(117, "dob"),
(3, "mobile");


To pivot the SSN field you can use this query:

select tkt.ticket_id,  tkt.number, SSN
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN   
            from os_form_entry_values val 
            where val.field_id in (4))  as SSN
   on tkt.ticket_id = SSN.entry_id
where tkt.ticket_id =5;

  +-----------+--------+--------+
  | ticket_id | number | SSN    |
  +-----------+--------+--------+
  |         5 | 592593 | 111111 |
  +-----------+--------+--------+


To put attribute value as column, we must join table. We can extends the previous query to include the other attributes:

select tkt.ticket_id,  tkt.number, SSN, title, dob, mobile
from os_TICKET tkt
inner join (select val.entry_id, val.value as SSN   
            from os_form_entry_values val 
            where val.field_id in (4))  as SSN
  on tkt.ticket_id = SSN.entry_id
inner join (select val.entry_id, val.value as title   
            from os_form_entry_values val 
            where val.field_id in (87))  as title
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as dob   
            from os_form_entry_values val 
            where val.field_id in (117))  as dob
  on tkt.ticket_id = title.entry_id
inner join (select val.entry_id, val.value as mobile   
            from os_form_entry_values val 
            where val.field_id in (3))  as mobile
  on tkt.ticket_id = title.entry_id
where ticket_id = 5;


  +-----------+--------+--------+-------+------------+---------------+
  | ticket_id | number | SSN    | title | dob        | mobile        |
  +-----------+--------+--------+-------+------------+---------------+
  |         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
  +-----------+--------+--------+-------+------------+---------------+
  1 row in set (0.00 sec)


The above query is using a static attributes (SSN, title, doc, mobile). 
If you have a static form that are never changing, the query can be working.
But if your form fields are changing, it is possible to
generate a dynamic sql using a stored procedure.

delimiter $$
CREATE PROCEDURE pivot(in tid int)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE field_name varchar(16);
  DECLARE field_id int;

  DECLARE names CURSOR FOR  
    select  FF.id, FF.name  
    from os_TICKET tkt 
     inner join os_form_entry_values val on tkt.ticket_id = val.entry_id 
     inner join os_form_field FF on val.field_id = FF.id
     where tkt.ticket_id = tid;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  set @sql_selector := 'select tkt.ticket_id, tkt.number ';
  set @sql_joins    := ' from os_TICKET tkt ';
  set @sql_where    := concat(' where ticket_id = ', tid);

  OPEN names;
  read_loop: LOOP
    FETCH names INTO field_id, field_name;
    select field_id, field_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    if field_id is not null and field_name is not null then
      set @sql_selector := concat(@sql_selector, ',', field_name);
      SET @sql_joins    := concat(@sql_joins,
       ' left join (select val.entry_id, val.value as ',field_name,   
                 ' from os_form_entry_values val 
                  where val.field_id in (', field_id,'))  as ', field_name,
                 ' on tkt.ticket_id = ', field_name ,'.entry_id');
    end if;
  END LOOP;
  close names;

  set @sql := concat(@sql_selector, @sql_joins, @sql_where);      
  PREPARE dynamic_statement FROM @sql;
  EXECUTE dynamic_statement;
  DEALLOCATE PREPARE dynamic_statement;  
END$$
delimiter ;


So if we call pivot the result must be the same of the previous query.

mysql&gt; call pivot(5);
+-----------+--------+--------+-------+------------+---------------+
| ticket_id | number | SSN    | title | dob        | mobile        |
+-----------+--------+--------+-------+------------+---------------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 |
+-----------+--------+--------+-------+------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


We can insert a new field:

insert into os_form_field values(200, 'new_field');
insert into os_form_entry_values values(5, 200, 'new_value');

mysql&gt; call pivot(5);
+-----------+--------+--------+-------+------------+---------------+-----------+
| ticket_id | number | SSN    | title | dob        | mobile        | new_field |
+-----------+--------+--------+-------+------------+---------------+-----------+
|         5 | 592593 | 111111 | Mr    | 05/01/2011 | 0091981123232 | new_value |
+-----------+--------+--------+-------+------------+---------------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


I advise you to refactor you database, or to do pivoting in your application. 
The previous queries and stored procedure could be very very inefficient.

Hope it help.