sql - 在 MS Access 中使用 SQL 进行分组和透视

Question

尝试旋转数据集时遇到问题(不确定这是否是转置？)

假设我有表 1:

Name       Food       Price  
 ------ -------------- ------- 
  Matt   Cheeseburger       5  
  Bill   Hotdog             4  
  Bill   Steak             10  
  Andy   Hotdog             4  
  Andy   Cheeseburger       5  
  Andy   Nachos             5  

我如何才能按名称分组，然后转置其他列以获得类似表 2 的内容？

表 2:

Name      Food_1         Food_2      Food_3   Price_1   Price_2   Price_3  
 ------ -------------- -------------- -------- --------- --------- --------- 
  Matt   Cheeseburger                                 5                      
  Bill   Hotdog         Steak                         4        10            
  Andy   Hotdog         Cheeseburger   Nachos         4         5         5 

谢谢!

Answer 1

这在 ms-access 中很奇怪，但您可以使用 subquery 来解决:

select t.name, 
       max(iff(rnk = 1, Food)) as food_1,
       max(iff(rnk = 2, Food)) as food_2,
       max(iff(rnk = 3, Food)) as food_3,
       max(iff(rnk = 1, Price)) as price_1,        
       max(iff(rnk = 2, Price)) as price_2,        
       max(iff(rnk = 3, Price)) as price_3        
from (select t.*,
             (select count(*)
              from table as t1
              where t1.name = t.name and t1.id <= t.id 
             ) as rnk
      from table as t
     ) as t
group by t.name;

这假设您的表有 identity 列，所以我只是用作 id。