sql - 将行透视到 Sql Server 中的列

Question

我有一个看起来像这样的示例表：

+------+-------------+---------+----------+
| Year |   Country   | Ranking | Category |
+------+-------------+---------+----------+
| 2018 | Ghana       |       1 | Swimming |
| 2018 | Sweden      |       2 | Swimming |
| 2018 | Costa Rica  |       3 | Swimming |
| 2018 | Jordan      |       1 | Sprint   |
| 2018 | Thailand    |       2 | Sprint   |
| 2018 | Finland     |       3 | Sprint   |
| 2018 | Myanmar     |       1 | Boxing   |
| 2018 | Peru        |       2 | Boxing   |
| 2018 | Belgium     |       3 | Boxing   |
| 2017 | Nigeria     |       1 | Swimming |
| 2017 | Philippines |       2 | Swimming |
| 2017 | Haiti       |       3 | Swimming |
| 2017 | Netherlands |       1 | Sprint   |
| 2017 | Macedonia   |       2 | Sprint   |
| 2017 | Kuwait      |       3 | Sprint   |
| 2017 | Malaysia    |       1 | Boxing   |
| 2017 | New Zealand |       2 | Boxing   |
| 2017 | Palau       |       3 | Boxing   |
+------+-------------+---------+----------+

并且需要创建一个如下所示的报告：

+----------+---------+-------------+------------+
| Category | Ranking |    2017     |    2018    |
+----------+---------+-------------+------------+
| Swimming |       1 | Nigeria     | Ghana      |
| Swimming |       2 | Philippines | Sweden     |
| Swimming |       3 | Haiti       | Costa Rica |
| Sprint   |       1 | Netherlands | Jordan     |
| Sprint   |       2 | Macedonia   | Thailand   |
| Sprint   |       3 | Kuwait      | Finland    |
| Boxing   |       1 | Malaysia    | Myanmar    |
| Boxing   |       2 | New Zealand | Peru       |
| Boxing   |       3 | Palau       | Belgium    |
+----------+---------+-------------+------------+

我搜索了数据透视样本，但似乎都使用了我认为不适用于我的情况的聚合函数。

这是我陷入困境的地方：

SELECT *
FROM
(
   SELECT Category
         ,Country
         ,Ranking
         ,[Year]
   FROM table1
 ) AS SourceTable PIVOT(<b><font color="red"> ?? </font></b> FOR [Year] IN ([2017], [2018])) AS PivotTable;

标签： sqlsql-servertsqlsql-server-2008

Answer 1

我认为条件聚合是实现预期结果的最简单方法。

您可以对Category和进行分组Ranking，然后使用 case 语句计算 2017 和 2018 列，最后null使用 删除值max：

declare @tmp table ([Year] int, Country varchar(50), Ranking int, Category varchar(50))
insert into @tmp values
     (2018, 'Ghana'       ,1, 'Swimming')
    ,(2018, 'Sweden'      ,2, 'Swimming')
    ,(2018, 'Costa Rica'  ,3, 'Swimming')
    ,(2018, 'Jordan'      ,1, 'Sprint')
    ,(2018, 'Thailand'    ,2, 'Sprint')
    ,(2018, 'Finland'     ,3, 'Sprint')
    ,(2018, 'Myanmar'     ,1, 'Boxing')
    ,(2018, 'Peru'        ,2, 'Boxing')
    ,(2018, 'Belgium'     ,3, 'Boxing')
    ,(2017, 'Nigeria'     ,1, 'Swimming')
    ,(2017, 'Philippines' ,2, 'Swimming')
    ,(2017, 'Haiti'       ,3, 'Swimming')
    ,(2017, 'Netherlands' ,1, 'Sprint')
    ,(2017, 'Macedonia'   ,2, 'Sprint')
    ,(2017, 'Kuwait'      ,3, 'Sprint')
    ,(2017, 'Malaysia'    ,1, 'Boxing')
    ,(2017, 'New Zealand' ,2, 'Boxing')
    ,(2017, 'Palau'       ,3, 'Boxing')

select 
      Category
    , Ranking 
    , max(case when [year] = 2017 then country else null end) as [2017] 
    , max(case when [year] = 2018 then country else null end) as [2018] 
From @tmp
group by 
    Category, Ranking 
order by 
    Category desc, Ranking 

输出：