行转换至列

IF OBJECT_ID('Orders') IS NOT NULL

  DROP TABLE Orders

GO

CREATE TABLE Orders

(

  ProductID INT NOT NULL,

  OrderDate DATETIME NOT NULL,

  ShipTo CHAR(20) NOT NULL,

  SubTotal MONEY NOT NULL

);

INSERT INTO Orders

VALUES(1,CAST('20090102' AS DATETIME),'SH',100);

INSERT INTO Orders

VALUES(1,CAST('20090105' AS DATETIME),'SH',100);

INSERT INTO Orders

VALUES(1,CAST('20090123' AS DATETIME),'JS',100);

INSERT INTO Orders

VALUES(2,CAST('20090106' AS DATETIME),'JS',100);

INSERT INTO Orders

VALUES(1,CAST('20090212' AS DATETIME),'ZJ',100);

INSERT INTO Orders

VALUES(1,CAST('20090222' AS DATETIME),'ZJ',100);

INSERT INTO Orders

VALUES(3,CAST('20090301' AS DATETIME),'SH',100);

INSERT INTO Orders

VALUES(3,CAST('20090214' AS DATETIME),'SH',100);

INSERT INTO Orders

VALUES(2,CAST('20090309' AS DATETIME),'SH',100);

SELECT * FROM Orders

SELECT ProductID,[1] AS 一月,[2] AS 二月,[3] AS 三月

FROM(SELECT ProductID,MONTH(OrderDate) AS OrderMonth,SubTotal FROM Orders)AS O

PIVOT

(

  SUM(SubTotal)

  FOR OrderMonth IN([1],[2],[3])

)AS PVT

ORDER BY ProductID