-3

I have data in the table that contains the surcharge price of many carriers in many years. Each carrier will have 12 months.

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

And I want to make a query in SQL server to get data like this.

Please note that: The data need to group by the year(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

I did search in google but can not find the solution. Pls give me how to do it.
Thank you so much.

Mr Thanh
  • 3
  • 5
  • No images please. The sample data is (almost) properly formatted, do the same with the expected result! – jarlh Aug 30 '21 at 13:02
  • 1
    Yes, i am edited the result data. Thank you so much. – Mr Thanh Aug 30 '21 at 13:04
  • 1
    this is a PIVOT. there are a few alternative methods. – Randy Aug 30 '21 at 13:06
  • Does this answer your question? [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – astentx Aug 30 '21 at 14:00
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 01 '21 at 09:52

1 Answers1

1

If you do know your list of carriers, you can do it like this with standard 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 and MONTH are usually reserved words, so it's not recommended to use them in your data.

James
  • 2,954
  • 2
  • 12
  • 25