行转列（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