行转列（PIVOT）,列转行 （UNPIVOT）

sql server ,oracle 通用：

1. 行转列（PIVOT）

CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), [Subject] NVARCHAR(30), [Score] FLOAT, ) INSERT INTO [StudentScores] SELECT 'alice', 'chinese', 80 INSERT INTO [StudentScores] SELECT 'alice', 'math', 90 INSERT INTO [StudentScores] SELECT 'alice', 'english', 70 INSERT INTO [StudentScores] SELECT 'alice', 'phy', 85 INSERT INTO [StudentScores] SELECT 'amy', 'chinese', 80 INSERT INTO [StudentScores] SELECT 'amy', 'math', 92 INSERT INTO [StudentScores] SELECT 'amy', 'english', 76 INSERT INTO [StudentScores] SELECT 'amy', 'phy', 88 INSERT INTO [StudentScores] SELECT 'miya', 'chinese', 60 INSERT INTO [StudentScores] SELECT 'miya', 'math', 82 INSERT INTO [StudentScores] SELECT 'miya', 'english', 96 INSERT INTO [StudentScores] SELECT 'miya', 'phy', 78

 

 

 

 

 

 

 

 

 

 

 

SELECT * FROM [StudentScores] /*source table*/
AS P
PIVOT
(
SUM(Score) FOR
p.Subject IN (chinese,math,english,phy)
) AS T

2. 列转行 （UNPIVOT）

CREATE TABLE ProgrectDetail ( ProgrectName NVARCHAR(20),  OverseaSupply INT,  NativeSupply INT,  SouthSupply INT, NorthSupply INT  ) INSERT INTO ProgrectDetail SELECT 'A', 100, 200, 50, 50 UNION ALL SELECT 'B', 200, 300, 150, 150 UNION ALL SELECT 'C', 159, 400, 20, 320

 

 

 

 

 

 

 

 

 

SELECT P.ProgrectName,P.Supplier,P.SupplyNum

FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P