如何以行的形式获取列数据

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‘operator.Since手动通过等级值。等级值不可预测，我需要根据它们的等级动态地将它们放在交叉表视图中。

我尝试过所有类型的动态交叉表生成查询(是的，我都看过了)，但没有任何成功。请帮帮我。谢谢!

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     |
 +------+-------+-------+-------+