MS SQL Server cross-report (Row-and-column interchange)

You can use the pivot operator in Ms-sqlserver 2005 to implement row and column conversions. , but you must use the case if then else end statement in the previous version

Below is an example of a Student score table:

ID Name Account Score

1  Zhang San   language  60
2  Zhang San   math  65
3  Zhang San   foreign language  70
4  John Doe   language  80
5  John Doe   Mathematics  90
6  John Doe   Foreign language  85
7  Harry   language  70
8  Harry   Math  71
9  Harry   Foreign language  75
10  Zhao Liu   language  64
11  Zhao Liu   Math  67
12  Zhao Liu   Foreign Languages  76 

The query concludes:

Name Language Mathematics Foreign language

Lee 480 90 85
Wang 570 71 75
Sheet 360 65 70
Zhao 664 67 76

Prepare the data:

if exists (select id from sysobjects where xtype= ' U ' and name= ' Studentscore ')
drop table studentscore--Delete the tables that conflict with the experiment
Go
CREATE table studentscore--Creating an experiment table
(
[id] int identity (+),
[name] nvarchar () NOT NULL,
Subject nvarchar () NOT NULL,
Score int NOT NULL
)
Go
--Add experimental data
Insert Studentscore values (' Zhang San ', ' language ', ' 60 ');
Insert Studentscore values (' Zhang San ', ' math ', ' 65 ');
Insert Studentscore values (' Zhang San ', ' foreign language ', ' 70 ');
Insert Studentscore VALUES (' John Doe ', ' language ', ' 80 ');
Insert Studentscore VALUES (' John Doe ', ' math ', ' 90 ');
Insert Studentscore VALUES (' John Doe ', ' foreign language ', ' 85 ');
Insert Studentscore values (' Harry ', ' language ', ' 70 ');
Insert Studentscore values (' Harry ', ' math ', ' 71 ');
Insert Studentscore values (' Harry ', ' foreign language ', ' 75 ');
Insert Studentscore values (' Zhao Liu ', ' language ', ' 64 ');
Insert Studentscore values (' Zhao Liu ', ' math ', ' 67 ');
Insert Studentscore values (' Zhao Liu ', ' foreign language ', ' 76 ');
Go
Select [ID], [name], subject, score from Studentscore
Go

1 Tom Chinese60
2 Tom Mathematical65
3 Tom Foreign language70
4 John doe Chinese80
5 John doe Mathematical90
6 John doe Foreign language85
7 Harry Chinese70
8 Harry Mathematical71
9 Harry Foreign language75
10 Zhao Liu Chinese64
11 Zhao Liu Mathematical67
12 Zhao Liu Foreign language76

First use the case and then else end statement to convert rows to columns:

Select [name],[Language]=sum (case when subject= ' language ' then score else null end),
[Math]=sum (case when subject= ' math ' then score else null end),
[Foreign Language]=sum (case when subject= ' foreign language ' then score else null end)
From Studentscore group by [name]

Query Result:

John Doe 80 90 "
Harry 70 71 
Zhang San 60 65 70
Zhao Liu 64 67 

above query role is also very large, for many cases, such as the product sales table in accordance with the quarterly statistics, according to the column header content fixed by the month, so that the line, but often most of the following header content is not fixed, like city, users may at any time delete, Add some cities, this is what we call dynamic cross-table, This time you need to spell out the SQL statement.

sqlserver local variable assignment method
has two kinds:  
One: set @ variable name = value  
Two kinds: SELECT @ variable name = value  

The second one can get the data from a table and then assign the value to the variable  
Example: The user name in the User Information table for CID 20 assigns him to the variable name 
declare @name varchar (10)-username  
Select @name =username from userInfo where cid = 20 
print ' CID is 20 user name: ' + @name  

Recursive Select variable: The br> recursive select variable refers to using a SELECT statement and a subquery to stitch together a variable with itself. The syntax form is as follows: Select @variable = @variable + table.column from table

DECLARE @sql varchar (max)
Set @sql = ' SELECT [Name], '
Select @sql = @sql + ' sum (case subject "+subject+"
Then score else null end) as ' +subject+ ' ', '
From (select distinct subject from Studentscore) as a
Select @sql = Left (@sql, Len (@sql)-1) + ' from Studentscore Group by [name] '
EXEC (@sql)

Execution Result:

John Doe 90 85
Harry 71 75  
Zhang San 65 70 
Zhao Liu 67 76 

MS SQL Server cross-report (Row-and-column interchange)