CrossTab

--Script Start--

Drop table TmpData_2021;

Create table TmpData_2021 (
ID int identity(1,1) primary key,
RM varchar(20) not null,
Month varchar(5) not null,
Price Decimal(20,4) not null,
KG decimal(20,4) not null)

--Insert Sample data
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM001','Jan','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM002','Jan','1000','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM003','Jan','1000','1.1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM004','Jan','1000','1.4')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM005','Jan','1000','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM006','Jan','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM007','Jan','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM008','Jan','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM009','Jan','1000','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM010','Jan','1000','1.3')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM011','Feb','1000','1.1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM012','Feb','1500','1.5')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM001','Feb','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM002','Feb','1200','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM003','Feb','1100','1.1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM004','Feb','1400','1.4')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM005','Feb','1200','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM006','Feb','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM007','Feb','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM008','Feb','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM009','Feb','1200','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM010','Feb','1300','1.3')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM005','Mar','1200','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM006','Mar','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM007','Mar','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM008','Mar','1000','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM009','Mar','1200','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM010','Mar','1300','1.3')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM011','Mar','1100','1.1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM012','Mar','2250','1.5')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM005','Mar','1300','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM006','Mar','1300','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM007','Mar','1500','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM008','Mar','1200','1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM009','Mar','1100','1.2')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM010','Mar','1000','1.3')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM011','Mar','1500','1.1')
Insert into TmpData_2021 (RM, Month, Price, KG )values ( 'RM012','Mar','2550','1.5')

--Test data
SELECT TOP (1000) [ID],[RM],[Month],[Price],[KG]
  FROM [MARKETING].[Commercial_Control].[TmpData_2021]
  --where rm = 'RM007'
--Order more in Mar
SELECT  [rm], month, sum(price) TotOrder, sum(kg) TotKG
  FROM [MARKETING].[Commercial_Control].[TmpData_2021]
  --where rm = 'RM007'
  GROUP BY [rm],month
--How to calculate price/kg in sql command and pivot by month
/*
RM         Jan        Feb          Mar
RM001   1000.0     1000.0
RM002    833.3     1000.0
RM003    909.1     1000.0
RM004    714.3     1000.0
RM005    833.3     1000.0      1041.7
RM006   1000.0     1000.0      1150.0
RM007   1000.0     1000.0      1250.0
RM008   1000.0     1000.0      1100.0
RM009    833.3     1000.0       958.3
RM010    769.2     1000.0       884.6
RM011               909.1      1181.8
RM012              1000.0      1600.0
*/
SELECT NT.RM, [Jan], [Feb], [Mar]
from (
    SELECT  [rm], month, sum(price) TotOrder, sum(kg) TotKG
    FROM [TmpData_2021]
    GROUP BY [RM], month
) as P
-- need total order summation  / total weight summation But Error this line
PIVOT (
SUM(TotOrder) FOR month IN ([Jan], [Feb], [Mar])

) AS NT
GROUP BY NT.RM, [Jan], [Feb], [Mar]
ORDER BY NT.RM