SQL – How to transpose?

by

MySQL doesn’t support ANSI PIVOT/UNPIVOT syntax, so that leave you to use:

  SELECT t.userid
         MAX(CASE WHEN t.fieldname="Username" THEN t.fieldvalue ELSE NULL END) AS Username,
         MAX(CASE WHEN t.fieldname="Password" THEN t.fieldvalue ELSE NULL END) AS Password,
         MAX(CASE WHEN t.fieldname="Email Address" THEN t.fieldvalue ELSE NULL END) AS Email
    FROM TABLE t
GROUP BY t.userid

As you can see, the CASE statements need to be defined per value. To make this dynamic, you’d need to use MySQL’s Prepared Statement (dynamic SQL) syntax.

More Related Contents:

  1. MySQL query finding values in a comma separated string
  2. In MySQL queries, why use join instead of where?
  3. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  4. Can table columns with a Foreign Key be NULL?
  5. MySQL not using indexes with WHERE IN clause?
  6. MySQL dump by query
  7. single fixed table with multiple columns vs flexible abstract tables
  8. How many rows in a database are TOO MANY?
  9. SQLite – ORDER BY RAND()
  10. Pivot Table and Concatenate Columns
  11. Which SQL query is better, MATCH AGAINST or LIKE?
  12. MySQL equivalent of DECODE function in Oracle
  13. How to delete Duplicates in MySQL table
  14. Best database field type for a URL
  15. Query that ignore the spaces
  16. How to Optimize Queries in a Database – The Basics
  17. MySQL – how to front pad zip code with “0”?
  18. Create date from day, month, year fields in MySQL
  19. Find rows that have the same value on a column in MySQL
  20. what is the difference between GROUP BY and ORDER BY in sql
  21. How to transform vertical data into horizontal data with SQL?
  22. How do I list all the columns in a table?
  23. Two single-column indexes vs one two-column index in MySQL?
  24. VARCHAR as foreign key/primary key in database good or bad?
  25. How to find all IDs of children recursively?
  26. Transpose latest rows per user to columns
  27. Splitting the string in sql server
  28. Need to list all triggers in SQL Server database with table name and table’s schema
  29. What is the purpose of constraint naming
  30. Multiple Ranks in one table

Leave a Comment