CODEWITHSUNDEEP
mysqlsqlpivot-table
benpva16
benpva16

Reputation: 454

MySQL - Make columns out of rows

I have a table of tree diameters like this:

TreeID  Diameter
----------------
1       3
1       2
1       3
2       2
2       2
2       1
1       3

I would like to get up to six diameters for each tree in descending order like this:

TreeID  Diameter 1  Diameter 2  Diameter 3  Diameter 4  Diameter 5  Diameter 6
------------------------------------------------------------------------------
1       3           3           3           2
2       2           2           1

I have a query to get Diameters 1 through 6:

(SELECT Diameter FROM TreeDiameters ORDER BY Diameter DESC LIMIT 1 OFFSET 1) AS Diameter1

but I need to put in a WHERE clause so that the TreeID in this subselect matches the TreeID in the main SELECT. What is that WHERE clause?

Upvotes: 1

Views: 95

Answers (2)

Strawberry
Strawberry

Reputation: 33935

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(measurement_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,TreeID  INT NOT NULL
,Diameter INT NOT NULL
);

INSERT INTO my_table (treeid,diameter) VALUES
(1      , 3),
(1      , 2),
(1      , 3),
(2      , 2),
(2      , 2),
(2      , 1),
(1      , 3);

SELECT x.*
     , COUNT(*) rank
  FROM my_table x
  JOIN my_table y
    ON y.treeid = x.treeid
   AND 
     ( y.diameter > x.diameter
       OR (y.diameter = x.diameter AND y.measurement_id <= x.measurement_id )
     )
  GROUP 
     BY x.treeid, x.diameter, x.measurement_id
-- HAVING rank < something
  ORDER BY treeid,rank;

  +----------------+--------+----------+------+
  | measurement_id | TreeID | Diameter | rank |
  +----------------+--------+----------+------+
  |              1 |      1 |        3 |    1 |
  |              3 |      1 |        3 |    2 |
  |              7 |      1 |        3 |    3 |
  |              2 |      1 |        2 |    4 |
  |              4 |      2 |        2 |    1 |
  |              5 |      2 |        2 |    2 |
  |              6 |      2 |        1 |    3 |
  +----------------+--------+----------+------+

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The easiest way is if you put the diameters in a single column, separated by a delimiter. Then you can use group_concat():

SELECT TreeId, substring_index(group_concat(Diameter order by Diameter desc), ',', 6)
FROM TreeDiameters
GROUP BY TreeId
ORDER BY Diameter DESC;

Six actual columns requires a bit more work.

Upvotes: 1

Related Questions