mysqlpivot-tablecolumnsorting

How can I get an ordered list of columns values in a row in MySQL?


I have a table that listed six unique numbers in a row, on their specified columns:

id n1 n2 n3 n4 n5 n6
1 44 11 32 14 28 19

How I can use MySQL to get the row values in an ascending order?

id n1 n2 n3 n4 n5 n6
1 11 14 19 28 32 44

Thank you!

I tried ORDER BY FIELD(), subqueries and concatenation, but nothing works.

SELECT aa.*, 
(SELECT CONCAT(n1,",",n2,",",n3,",",n4,",",n5,",",n6) FROM table bb WHERE bb.id=aa.id ORDER BY FIELD(n1,n2,n3,n4,n5,n6) asc) AS conc 
FROM table aa 
WHERE aa.id=1

I know is a childish approach, but I have no idea how to get the right result.


Solution

  • SELECT id,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 1), ',', -1) AS n1,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 2), ',', -1) AS n2,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 3), ',', -1) AS n3,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 4), ',', -1) AS n4,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 5), ',', -1) AS n5,
           SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(n ORDER BY n), ',', 6), ',', -1) AS n6
    FROM (
        SELECT id, n1 AS n FROM table
        UNION ALL
        SELECT id, n2 AS n FROM table
        UNION ALL
        SELECT id, n3 AS n FROM table
        UNION ALL
        SELECT id, n4 AS n FROM table
        UNION ALL
        SELECT id, n5 AS n FROM table
        UNION ALL
        SELECT id, n6 AS n FROM table
        ) AS unpivotted
    GROUP BY id