title: mysql pivot/crosstab query
body: <p><strong>Question 1:</strong> I have a table with the below structure and data:</p>
  
  <pre><code>app_id  transaction_id  mobile_no   node_id  customer_attribute  entered_value 
  100     111             9999999999  1        Q1                  2                             
  100     111             9999999999  2        Q2                  1                             
  100     111             9999999999  3        Q3                  4                             
  100     111             9999999999  4        Q4                  3                             
  100     111             9999999999  5        Q5                  2                             
  100     222             8888888888  4        Q4                  1                             
  100     222             8888888888  3        Q3                  2                             
  100     222             8888888888  2        Q2                  1                             
  100     222             8888888888  1        Q1                  3                             
  100     222             8888888888  5        Q5                  4                             
  </code></pre>
  
  <p>I want to display these records in the below format:</p>
  
  <pre><code>app_id  |  transaction_id  | mobile     |  Q1  |  Q2  |  Q3  |  Q4 |  Q5  |
   100    |      111         | 9999999999 |   2  |   1  |   4  |  3  |  2   |
   100    |      222         | 8888888888 |   3  |   1  |   2  |  1  |  4   |
  </code></pre>
  
  <p>I know I need to use crosstab/pivot query to get this display. For this I tried it based on the limited knowledge that I have about it. Following is my query:</p>
  
  <pre><code>SELECT app_id, transaction_id, mobile_no,
    (CASE node_id WHEN 1 THEN entered_value ELSE '' END) AS user_input1,
    (CASE node_id WHEN 2 THEN entered_value ELSE '' END) AS user_input2,
    (CASE node_id WHEN 3 THEN entered_value ELSE '' END) AS user_input3,
    (CASE node_id WHEN 4 THEN entered_value ELSE '' END) AS user_input4,
    (CASE node_id WHEN 5 THEN entered_value ELSE '' END) AS user_input5
  FROM trn_user_log 
  GROUP BY app_id, transaction_id, mobile_no, node_id
  </code></pre>
  
  <p>And based on this query I got the below display:</p>
  
  <pre><code>app_id  transaction_id  mobile_no   user_input1  user_input2  user_input3  user_input4  user_input5  
  100     111             9999999999  2                                                                
  100     111             9999999999               1                                                   
  100     111             9999999999                            4                                      
  100     111             9999999999                                         3                         
  100     111             9999999999                                                      2            
  100     222             8888888888  3                                                                
  100     222             8888888888               1                                                   
  100     222             8888888888                            2                                      
  100     222             8888888888                                         1                         
  100     222             8888888888                                                      4            
  </code></pre>
  
  <p>Can anyone help me with the proper changes that I need to make to my query to get the records in one single row and not multiple rows as above.</p>
  
  <p><strong>Question 2:</strong> Also is there a way to get the value of a particular field as the NAME of the column. As you can see above I have <code>user_input1</code>, <code>user_input2</code>,... as the header. Instead of that I want to have the values in <code>customer_attribute</code> as the header of the columns. </p>
  
  <p>For this I checked <code>NAME_CONST(name,value)</code> as below:</p>
  
  <pre><code>SELECT app_id, transaction_id, mobile_no,
  NAME_CONST(customer_attribute, (CASE node_id WHEN 1 THEN entered_value ELSE '' END))
  FROM trn_user_log 
  </code></pre>
  
  <p>But it gives an error </p>
  
  <pre><code>Error Code : 1210 Incorrect arguments to NAME_CONST
  </code></pre>
  
  <p>Help required.</p>