时间戳

首页 > 解决方案 > 在 Pivot 中如何消除重复项

sql-server - 在 Pivot 中如何消除重复项

问题描述

转动前

 Year  | RainDays |
-------+----------+
 2012  |      112 |
 2013  |      116 |
 2014  |      111 |
 2015  |       80 |
 2016  |      110 |
 2017  |      102 |
 2018  |      80  |
 2019  |      110 |

旋转后

 2012 | 2013 | 2014 | 2015 | 2016 |  2017| 2018 | 2019 |  
 -----+------+------+------+------+------+------+------+
 112  |  116 |  111 |   80 |  110 |  102 |  0.0 |  0.0 |

如果任何数字在枢轴中第二次重复，则应设为 0.0

下面是枢轴的SQL

SELECT [1], [2], [3], [4], [5], [6], [7]    
FROM  
(
Select row_number () over(Order by [Year] asc) Rn,Raindays
from Rain
) AS SourceTable  
PIVOT  
(  
Max(Raindays)
FOR Rn IN ( [1], [2], [3], [4], [5], [6], [7])  
) AS PivotTable

标签： sql-server

解决方案

您可以尝试如下使用ROW_NUMBER().

;with cte as
(
 select *, ROW_NUMBER() over(partition by raindays order by year) rn
 from @mytable
)
SELECT *
FROM  
(
 select Year, case when rn >1 then 0 else RainDays end as RainDays from cte 
)t 
PIVOT  
(  
max(RainDays)  
FOR Year IN ([2012], [2013], [2014], [2015], [2016],[2017],[2018],[2019])  
) AS PivotTable;

在线演示

输出

+------+------+------+------+------+------+------+------+
| 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 |
+------+------+------+------+------+------+------+------+
| 112  | 116  | 111  | 80   | 110  | 102  | 0    | 0    |
+------+------+------+------+------+------+------+------+

推荐阅读