mysql pivot/crosstab query

by

While @John’s static answer works great, if you have an unknown number of columns that you want to transform, I would consider using prepared statements to get the results:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT((CASE node_id when ',
      node_id,
      ' then entered_value else NULL END)) AS user_input',
      node_id
    )
  ) INTO @sql
FROM trn_user_log;


SET @sql = CONCAT('SELECT app_id, transaction_id, mobile_no, ', @sql, ' 
                  FROM trn_user_log 
                  GROUP BY app_id, transaction_id, mobile_no');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

see SQL Fiddle with Demo

As far as your second, please clarify what you are trying to do it is not clear.

More Related Contents:

  1. How can I return pivot table output in MySQL?
  2. MySQL pivot row into dynamic number of columns
  3. Mysql query to dynamically convert rows to columns
  4. MySQL dynamic-pivot
  5. Mysql Convert Column to row (Pivot table )
  6. Mysql query to dynamically convert rows to columns on the basis of two columns
  7. How to transpose mysql table rows into columns
  8. Setting column values as column names in the SQL query result
  9. MySQL Pivot Table Column Data as Rows
  10. Recreating a query using INNER JOIN? [duplicate]
  11. Create a Cumulative Sum Column in MySQL
  12. Equivalent of explode() to work with strings in MySQL
  13. MySQL Trigger after update only if row has changed
  14. MySQL “WITH” clause
  15. mysql select dynamic row values as column names, another column as value
  16. MySQL “Group By” and “Order By”
  17. MySQL DELETE FROM with subquery as condition
  18. MySQL Query to select data from last week?
  19. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  20. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  21. Find most frequent value in SQL column
  22. How to convert number of week into date?
  23. update multiple rows using limit in mysql?
  24. Mysql 1050 Error “Table already exists” when in fact, it does not
  25. How to get the latest record in each group using GROUP BY? [duplicate]
  26. How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
  27. SQL unique varchar case sensitivity question
  28. How do I count columns of a table
  29. MySQL 5.6 – DENSE_RANK like functionality without Order By
  30. ORDER BY alphabet first then follow by number

Leave a Comment