在sql server中选择表中的动态列



表中有很多运营商多年的附加费价格。每个承运人将有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

YEARMONTH通常是保留字,因此不建议在数据中使用它们。

相关内容

  • 没有找到相关文章

最新更新