title: Creating a new column in mysql view from an existing value
body: <p>I am incredibly new at this so let me know if I can clarify. Essentially I am looking to create a new column in a mysql view based on values from a column in an existing table. I'm using phpGrid to output on the front end.</p>
  
  <p>For instance - let's say I have existing column <code>question_id</code>:</p>
  
  <pre><code>    name | question_id | answer
  ----------------------------------
      Joe  | 1           | yes
      Joe  | 2           | no
      Ann  | 1           | maybe
      Ann  | 2           | kinda
  </code></pre>
  
  <p>and I have custom view <code>my_view</code> where I want the columns to be as such (not necessarily with <code>question_id=</code> in front, but whatever works):</p>
  
  <pre><code>    name  | question_id=1 | question_id=2
  ------------------------------------------
      Joe   | yes           | no
      Ann   | maybe         | kinda
  </code></pre>
  
  <p>Or something to that effect. How do I get the <code>question_id</code> values to become columns, and the answers in the right place?</p>
  
  <p>Also, if <code>name</code> is from one table, and <code>question_id</code> is from another, how can I get the correctly associated values alongside each other in <code>my_view</code>? <code>name</code> values often have duplicates, but they have individual entry ids.</p>
  
  <p>Cheers and thanks!</p>
  
  <p><strong>EDIT:</strong> <a href="https://stackoverflow.com/questions/4842148/create-mysql-view-using-distinct-values-as-columns">Create MySQL view using distinct values as columns</a> is the right idea, but I get error #1064 about incorrect syntax. </p>
  
  <p>Also, I'm not sure it solves the second problem of getting the data to align by unique entry ID. I tried to use this: <a href="https://stackoverflow.com/questions/6638520/1052-column-id-in-field-list-is-ambiguous">1052: Column &#39;id&#39; in field list is ambiguous</a> to fix the ambiguous issue but then I get error #1060 that there is a duplicate.</p>
  
  <p><strong>EDIT 2:</strong> Here is the code I use to try to get the columns from two separate tables, including the shared <code>registration_id</code> column present in both. I got the code from the post above regarding solving the ambiguous issue:</p>
  
  <pre><code>CREATE VIEW access AS
      SELECT fname,lname,email,city,state,country_id,phone,payment_status,event_id,wp_events_attendee.registration_id,wp_events_answer.registration_id,question_id,answer
      FROM wp_events_answer
      JOIN wp_events_attendee ON wp_events_attendee.registration_id = wp_events_answer.registration_id 
  </code></pre>
  
  <p>It gives me "#1060 - Duplicate column name 'registration_id'". Also not sure how to integrate that into the code seen at <a href="https://stackoverflow.com/questions/4842148/create-mysql-view-using-distinct-values-as-columns">Create MySQL view using distinct values as columns</a>. I tried to do a test:</p>
  
  <pre><code>  CREATE VIEW access AS
  SELECT
      fname,lname,email,city,state,country_id,phone,payment_status,event_id,wp_events_attendee.registration_id,wp_events_answer.registration_id,question_id,answer,registration_id,
      MAX(IF(question_id = '1', status, NULL)) FIRST,
      MAX(IF(question_id = '11', status, NULL)) DOB,
  FROM wp_events_answer, wp_events_attendee
  JOIN wp_events_attendee ON wp_events_attendee.registration_id = wp_events_answer.registration_id 
  GROUP BY registration_id;
  </code></pre>
  
  <p>But got "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_events_answer, wp_events_attendee GROUP BY registration_id' at line 6". Thanks!</p>