create table test(id int,name varchar(20),quarter int,PRofile int) insert into test values(1,'a',1,1000)insert into test values(1,'a',2,2000)insert into test values(1,'a',3,4000)insert into test values(1,'a',4,5000)insert into test values(2,'b',1,3000)insert into test values(2,'b',2,3500)insert into test values(2,'b',3,4200)insert into test values(2,'b',4,5500)select * from testid name quarter profile----------- -------------- ----------- -----------1 a 1 10001 a 2 20001 a 3 40001 a 4 50002 b 1 30002 b 2 35002 b 3 42002 b 4 5500(8 row(s) affected)使用PIVOT將四個季度的利潤轉換成橫向顯示:select id,name,[1] as "一季度",[2] as "二季度",[3] as "三季度",[4] as "四季度"fromtestpivot(sum(profile)for quarter in([1],[2],[3],[4]))as pvtid name 一季度 二季度 三季度 四季度-------- --------- ----------- -------- ------- -------1 a 1000 2000 4000 50002 b 3000 3500 4200 5500(2 row(s) affected)

首先建立測試表，然后插入測試數據drop table testcreate table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)insert into test values(1,'a',1000,2000,4000,5000)insert into test values(2,'b',3000,3500,4200,5500)select * from testid name Q1 Q2 Q3 Q4-------- ------- --------- --------- -------- --------1 a 1000 2000 4000 50002 b 3000 3500 4200 5500(2 row(s) affected)使用UNPIVOT，將同一行中四個季度的列數據轉換成四行數據：select id,name,quarter,profilefromtestunpivot(profilefor quarter in([Q1],[Q2],[Q3],[Q4])) as unpvtid name quarter profile----------- ----------- ---------- -----------1 a Q1 10001 a Q2 20001 a Q3 40001 a Q4 50002 b Q1 30002 b Q2 35002 b Q3 42002 b Q4 5500(8 row(s) affected)