表中有很多运营商多年的附加费价格。每个承运人将有12个月。
CARRIER_ID YEAR MONTH RATE
DHL 2021 April 16.5
DHL 2021 August 18.5
DHL 2021 December 0
DHL 2021 February 14
DHL 2021 January 12.5
DHL 2021 July 17.75
DHL 2021 June 17
DHL 2021 March 15
DHL 2021 May 17
DHL 2021 November 0
DHL 2021 October 0
DHL 2021 September 0
FedEx 2021 April 16.5
FedEx 2021 August 17.5
FedEx 2021 December 0
FedEx 2021 February 14.5
FedEx 2021 January 13.5
FedEx 2021 July 17.5
FedEx 2021 June 17
FedEx 2021 March 16
FedEx 2021 May 16.5
FedEx 2021 November 0
FedEx 2021 October 0
FedEx 2021 September 0
我想在SQL server中查询得到这样的数据。
请注意:数据需要按年分组(exp: 2021)
Month DHL FedEx
January 12.50% 13.50%
February14.00% 14.50%
March 15.00% 16.00%
April 16.50% 16.50%
May 17.00% 16.50%
June 17.00% 17.00%
July 17.75% 17.50%
August 18.50% 17.50%
September0 0
October 0 0
November0 0
December0 0
我在谷歌搜索,但找不到解决方案。请告诉我怎么做。
非常感谢。
如果您知道您的载波列表,您可以这样做,使用标准sql
select
t.YEAR,
t.MONTH,
max(case when t.CARRIER_ID = 'DHL' then t.RATE else NULL) as DHL,
max(case when t.CARRIER_ID = 'FedEx' then t.RATE else NULL) as FedEx
from your_table t
group by t.YEAR, t.MONTH
order by t.YEAR, t.MONTH
YEAR
和MONTH
通常是保留字,因此不建议在数据中使用它们。