Column and column Inversion Techniques in SQLServer

Column-and-column inversion is a common technique in sqlserver. When using an application system, you often need to do some statistical functions to avoid using the column-and-column inversion technique, I made a small summary: the first type: Use case in sqlserver2000 to invert the rows and columns. createtableRowCellConvertTest (gradevarchar (50), sex

Column-and-column inversion is a common technique in SQL server. When using an application system, you often need to do some statistical functions to avoid using the column-and-column inversion technique, I made a small summary: first, SQL server 2000 uses case to put the rows and columns upside down create table RowCellConvertTest (grade varchar (50), sex

Column-and-column inversion is a common technique in SQL server. When using an application system, you often need to do some statistical functions to avoid using the column-and-column inversion technique, I made a summary:

First: Use case in SQL server 2000 to put the rows and columns upside down

Create table RowCellConvertTest
(
Grade varchar (50 ),
Sex varchar (50 ),
StudentCount int
)
Go
Insert into RowCellConvertTest
Select '1year', 'male', 100 union all
Select '1year', 'female ', 200 union all
Select '2', 'male', 100 union all
Select '2', 'female ', 100 union all
Select 'third grade', 'male', 200 union all
Select grade 3, female, 200
Go

Select * from RowCellConvertTest
Go

-- Convert rows into columns using SQL Server 2000 case
Declare @ SQL varchar (max)
Set @ SQL = 'select grade'
Select @ SQL = @ SQL + ', sum (case when sex = ''' + sex + ''' then studentCount else ''' end) as ''' + sex + ''' from RowCellConvertTest group by sex
-- Select @ SQL = substring (@ SQL, 2, len (@ SQL ))
-- Print @ SQL
Select @ SQL = @ SQL + 'from RowCellConvertTest group by grade'
Exec (@ SQL)
Go

Drop table RowCellConvertTest
Go

Effect:

Second:

-- Convert rows into columns using SQL Server 2005's new feature
Select grade, male, female
From
(
Select studentCount, sex, grade from RowCellConvertTest
) P
Bytes
(
Sum (studentCount)
FOR sex IN
(Male, female)
) AS pvt
Order by pvt. grade;

Additional: Convert columns into rows

-- Convert columns into rows
Create table CellRowConvertTest
(
Grade varchar (50 ),
Male varchar (50 ),
Female varchar (50)
)
Go
Insert into CellRowConvertTest
Select '1st year', 100,200 union all
Select '2nd year', 100,100 union all
Select grade 3, 200,200
Go

Select * from CellRowConvertTest
Go

Select grade, sex, studentCount
From
(
Select grade, male, female from CellRowConvertTest
) As p
Unregister
(
StudentCount for sex in (male, female)
) As unpvt;
Go

Drop table CellRowConvertTest
Go

Effect: