mysql - 如何将列数据获取为行数据

Question

I have the following problem in MySQL 5.5. Here's my table.

Suppose i have a table With 'Names' with columns Rank,NAME and data in table 

When i run the query it will give result as 

select *from name 

    Rank | NAME  
    -------------
     1   |   A  
     1   |   B  
     1   |   C 
     2   |   D
     2   |   E  
     2   |   F 
     3   |   G  
     3   |   H 
     3   |   I  

这当然是一种非常不方便的数据组织方式，但这就是数据恰好进来的方式(并将继续进来)。

我需要能够使用与其各自排名相对应的名称列表来抛出它，如下所示

    Rank  | Name    | Name  | Name
    -----------------------------------
      1   |   A     |  B    | C
      2   |   D     |  E    | F
      3   |   G     |  H    | I

我有这样的查询

select 
    case when rank=1 then name else null end as 1,
    case when rank=2 then name else null end as 2,
    case when rank=3 then name else null end as 3
    from name 

需要将具有相同排名的姓名带入并显示在同一行中。我无法估计学生将获得的最后排名，因此我无法使用“IN”运算符手动传递排名值。因为排名值是不可预测的我需要根据他们的排名动态地让他们进入交叉 TableView 。

我尝试过各种动态交叉表生成查询(是的，我已经看过它们全部)，但没有任何成功。请帮我。谢谢!

Answer 1

DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (rank INT NOT NULL
 ,name CHAR(1) NOT NULL
 ,PRIMARY KEY(rank,name)
 );

 INSERT INTO my_table VALUES
 (1   ,'A'),
 (1   ,'B'),
 (1   ,'C'),
 (2   ,'D'),
 (2   ,'E'),
 (2   ,'F'),
 (3   ,'G'),
 (3   ,'H'),
 (3   ,'I');

 SELECT * FROM my_table;
 +------+------+
 | rank | name |
 +------+------+
 |    1 | A    |
 |    1 | B    |
 |    1 | C    |
 |    2 | D    |
 |    2 | E    |
 |    2 | F    |
 |    3 | G    |
 |    3 | H    |
 |    3 | I    |
 +------+------+

 SELECT x.*,COUNT(*) subrank FROM my_table x JOIN my_table y ON y.rank = x.rank AND y.name <= x.name GROUP BY x.rank,x.name;
 +------+------+---------+
 | rank | name | subrank |
 +------+------+---------+
 |    1 | A    |       1 |
 |    1 | B    |       2 |
 |    1 | C    |       3 |
 |    2 | D    |       1 |
 |    2 | E    |       2 |
 |    2 | F    |       3 |
 |    3 | G    |       1 |
 |    3 | H    |       2 |
 |    3 | I    |       3 |
 +------+------+---------+

 SELECT rank
      , MAX(CASE WHEN subrank = 1 THEN name END) name1
      , MAX(CASE WHEN subrank = 2 THEN name END) name2
      , MAX(CASE WHEN subrank = 3 THEN name END) name3
   FROM     
      ( SELECT x.*
             , COUNT(*) subrank 
          FROM my_table x 
          JOIN my_table y  
            ON y.rank = x.rank 
           AND y.name <= x.name 
         GROUP  
            BY x.rank
             , x.name
      ) a
  GROUP 
     BY rank;

 +------+-------+-------+-------+
 | rank | name1 | name2 | name3 |
 +------+-------+-------+-------+
 |    1 | A     | B     | C     |
 |    2 | D     | E     | F     |
 |    3 | G     | H     | I     |
 +------+-------+-------+-------+