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

我已经搜索了pivot示例，但似乎都使用了聚合函数，我认为这些聚合函数不适用于我的情况。

这就是我被困住的地方：

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;

Answer 1

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

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

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 

输出：

​

​