SQL Server row/column inversion example

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

Sample Database and test data

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 RowCellConvertDemo
Bytes
(
Sum (profile) for quarter in ([1], [2], [3], [4])
) As pvt

Result:

 

 

2. Use unregister for reverse operations

Sample Database and test data

Create table CellRowConvertDemo (id int, name varchar (50), Q1 int, Q2 int, Q3 int, Q4 int)
Insert into CellRowConvertDemo values (1, 'A)
Insert into CellRowConvertDemo values (2, 'B', 3000,3500, 4200,5500)

CellRowConvertDemo data:

Use unregister for reverse operations

Select id, [name], quarter, profile
From CellRowConvertDemo
Unregister
(
Profile for quarter in ([Q1], [Q2], [Q3], [Q4])
) As unpvt

Result: