PIVOT行转列

--测试数据
CREATE TABLE TScore
(
    FID INT IDENTITY(1,1),
    FName NVARCHAR(30),
    FType NVARCHAR(10),
    FScore DECIMAL(10,1)
)
GO

INSERT INTO TScore(FName,FType,FScore)
VALUES(N'AA',N'语文',30)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'AA',N'数学',40)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'AA',N'英语',50)

INSERT INTO TScore(FName,FType,FScore)
VALUES(N'BB',N'语文',70)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'BB',N'数学',60)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'BB',N'英语',90)

INSERT INTO TScore(FName,FType,FScore)
VALUES(N'CC',N'语文',55.5)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'CC',N'数学',65.5)
INSERT INTO TScore(FName,FType,FScore)
VALUES(N'CC',N'英语',75.5)

GO

 

SELECT * FROM TScore

 

 

;WITH cteScore
AS
(
    SELECT FName,FType,FScore 
    FROM TScore
)

SELECT * FROM cteScore PIVOT(MAX([FScore]) FOR FType IN([语文],[数学],[英语])) AS pvt

 

;WITH cteScore2
AS
(
    SELECT FName,FType,FScore 
    FROM TScore
    WHERE FScore > 40
)

SELECT * FROM cteScore2 PIVOT(MAX([FScore]) FOR FType IN([语文],[数学],[英语])) AS pvt

 

 

;WITH cteScore3
AS
(
    SELECT FID,FName,FType,FScore 
    FROM TScore
)

SELECT * FROM cteScore3 PIVOT(MAX([FScore]) FOR FType IN([语文],[数学],[英语])) AS pvt