MySQL Join Multiple Rows as Columns

by

An INNER JOIN will suffice your needs. MySQL has no PIVOT function by you can still simulate it using CASE and MAX() function.

SELECT  a.ID, a.NAME,
        MAX(CASE WHEN b.Race_Number = 1 THEN b.Place ELSE NULL END) Race1,
        MAX(CASE WHEN b.Race_Number = 2 THEN b.Place ELSE NULL END) Race2,
        MAX(CASE WHEN b.Race_Number = 3 THEN b.Place ELSE NULL END) Race3
FROM    Table1 a
        INNER JOIN Table2 b
            ON a.ID = b.ID
GROUP   BY a.ID, a.Name

But if you have unknown number of RACE, then a DYNAMIC SQL is much more preferred.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN b.Race_Number=", Race_Number,
      " THEN b.Place END) AS ', CONCAT('`Race', Race_Number, '`'))
    ) INTO @sql
FROM Table2;

SET @sql = CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM Table1 a
                   LEFT JOIN Table2 b 
                        ON ON a.ID = b.ID
                   GROUP   BY a.ID, a.Name');

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

More Related Contents:

  1. Order results from two queries based on time in second query [closed]
  2. Query database then email posts from where user is subbed to
  3. Using PHP data in MySQL [closed]
  4. Save the data into a database and return another value from database using PHP
  5. Checking the availability of rooms
  6. How can I do ‘insert if not exists’ in MySQL?
  7. How to properly set up a PDO connection
  8. Getting raw SQL query string from PDO prepared statements
  9. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  10. MySQL Prepared statements with a variable size variable list
  11. Loading .sql files from within PHP
  12. PDO with INSERT INTO through prepared statements [closed]
  13. CakePHP find method with JOIN
  14. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  15. Comma separated values in MySQL “IN” clause
  16. MySQL “NOT IN” query 3 tables
  17. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  18. PHP: multiple SQL queries in one mysql_query statement
  19. Doing a while / loop to get 10 random results
  20. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  21. Why does this return Resource id #2? [duplicate]
  22. Simple PHP SQL login troubleshooting
  23. Change date format (in DB or output) to dd/mm/yyyy – PHP MySQL
  24. MySql PHP select count of distinct values from comma separated data (tags)
  25. How to SELECT DEFAULT value of a field
  26. Laravel Eloquent get results grouped by days
  27. Do I have to use mysql_real_escape_string if I bind parameters?
  28. SELECT row by DATEPART()
  29. UNION syntax in Cakephp
  30. How do i search from serialize field in mysql database?

Leave a Comment