Rank-and-column inversion is one of the commonly used techniques in sqlserver. Unlike sqlserver2000's case-based splicing method, sqlserver2005 provides the limit and unlimit keywords to implement this technique.
1. Use the marker to put the rows and columns upside down
create table RoleCellConvertDemo(id int,name varchar(20),quarter int,profile int)insert into RoleCellConvertDemo values(1,'a',1,1000)insert into RoleCellConvertDemo values(1,'a',2,2000)insert into RoleCellConvertDemo values(1,'a',3,4000)insert into RoleCellConvertDemo values(1,'a',4,5000)insert into RoleCellConvertDemo values(2,'b',1,3000)insert into RoleCellConvertDemo values(2,'b',2,3500)insert into RoleCellConvertDemo values(2,'b',3,4200)insert into RoleCellConvertDemo values(2,'b',4,5500)
The data in the table rolecellconvertdemo is as follows:
Use the histogram to convert the profit of each quarter into a horizontal display:
Select ID number, [name] Name, [1] First Quarter, [2] Second Quarter, [3] third quarter, [4] Fourth quarter from rowcellconvertdemopivot (sum (profile) for quarter in ([1], [2], [3], [4]) as PVT
Result:
2. Use unregister for reverse operations
create table CellRowConvertDemo(id int,name varchar(50),Q1 int,Q2 int,Q3 int,Q4 int)insert into CellRowConvertDemo values(1,'a',1000,2000,4000,5000)insert into CellRowConvertDemo values(2,'b',3000,3500,4200,5500)
Cellrowconvertdemo data:
Use unregister for reverse operations
select id,[name],quarter,profilefrom CellRowConvertDemounpivot(profile for quarter in([Q1],[Q2],[Q3],[Q4]))as unpvt
Result: