mysql - SQL，使用数学将行转换为列

Question

我有这张表:

Player    Routes   Colors

 GIN         2      RED    
 GIN         1      BLU    
 GIN         5      BLAS    
 GIN         1      TREN    
 GIN         2      RED    
 PON         1      TREN
 PON         4      BLU

来自带有 union all 的查询，如下所示:

select *
from (
        select  DISTINCT Colors.Name [Colors]
              , COUNT(routes.idRoutes) [Routes]
              , Routes.cdRoutes [Doc]
        from Routes
        inner join Colors on Colors.Name blablabla
        where blablabla
        group by Colors.Name, Routes.cdRoutes

    ) dates

我需要一个像这样的表格:

Colors    GIN    PON

 RED       4      0 
 BLU       1      4
 BLAS      5      0
 TREN      1      1

如何做？我想我可以不使用PIVOT函数来获取它，对吗？

Answer 1

你可以这样做(即使不使用 PIVOT):

select
    Colors,
    sum(case when Player = 'GIN' then Routes else 0 end) as GIN,
    sum(case when Player = 'PON' then Routes else 0 end) as PON
from your_query_or_Table
group by Colors