PIVOT IN MSSQL

Pivot in mssql with very simple example
		
CREATE TABLE currency_master
(
	id					BIGINT IDENTITY(1,1) PRIMARY KEY,
	currency_code		INT,
	currency_desc		VARCHAR(100),
	short_name			VARCHAR(10)
)

INSERT INTO currency_master
SELECT 1 currency_code,'Indial Rupee' currency_desc,'INR' short_name ;
INSERT INTO currency_master
SELECT ISNULL(MAX(currency_code),0)+1 currency_code,'United Arab Emirates Dirham' currency_desc,'AED' short_name FROM currency_master;
INSERT INTO currency_master
SELECT ISNULL(MAX(currency_code),0)+1 currency_code,'British pound sterling' currency_desc,'GBP' short_name FROM currency_master;
INSERT INTO currency_master
SELECT ISNULL(MAX(currency_code),0)+1 currency_code,'United States Dollar' currency_desc,'USD' short_name FROM currency_master;
INSERT INTO currency_master
SELECT ISNULL(MAX(currency_code),0)+1 currency_code,'Euro' currency_desc,'EUR' short_name FROM currency_master;
INSERT INTO currency_master
SELECT ISNULL(MAX(currency_code),0)+1 currency_code,'Japanese yen' currency_desc,'JPY' short_name  FROM currency_master;


CREATE TABLE forex_currency_rate_master
(
	id					BIGINT IDENTITY(1,1) PRIMARY KEY,
	currency_code		INT,
	rate_date			DATETIME,
	currency_rate		DECIMAL(18,2)
)

INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-11-30' AS Date), 2, CAST(84.452895 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-11-30' AS Date), 3, CAST(97.753206 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-11-30' AS Date), 4, CAST(0.589500 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-11-30' AS Date), 5, CAST(22.215355 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-11-30' AS Date), 6, CAST(81.597000 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-01' AS Date), 6, CAST(81.151200 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-01' AS Date), 2, CAST(84.746200 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-01' AS Date), 3, CAST(98.168600 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-01' AS Date), 5, CAST(22.094000 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-01' AS Date), 4, CAST(0.594900 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-02' AS Date), 6, CAST(81.177100 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-02' AS Date), 2, CAST(85.463300 AS Decimal(18, 6)))
INSERT forex_currency_rate_master ( rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-02' AS Date), 3, CAST(99.312100 AS Decimal(18, 6)))
INSERT forex_currency_rate_master (rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-02' AS Date), 5, CAST(22.101000 AS Decimal(18, 6)))
INSERT forex_currency_rate_master (rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-02' AS Date), 4, CAST(0.600700 AS Decimal(18, 6)))
INSERT forex_currency_rate_master (rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-06' AS Date), 6, CAST(82.280000 AS Decimal(18, 6)))
INSERT forex_currency_rate_master (rate_date, currency_code, currency_rate) VALUES (CAST(N'2022-12-06' AS Date), 2, CAST(86.254100 AS Decimal(18, 6)))

SELECT 
	* 
FROM
(
	SELECT 
		cr.rate_date,c.short_name,cr.currency_rate
	FROM 
		currency_master c
		INNER JOIN forex_currency_rate_master cr
			ON c.currency_code=cr.currency_code
) c
PIVOT
(
	AVG(currency_rate) FOR short_name IN(USD,EUR,GBP,AED,JPY)
) a
ORDER BY rate_date