MS SQLServer Cross report (Row/column swaps), mssqlserver

MS SQLServer Cross report (Row/column swaps), mssqlserver

In MS-SQLServer 2005, you can use the combine operator to convert rows and columns. However, you must use the case when then else end statement in earlier versions.

The student renewal table is used as an example below:

Id name subject score

1 Zhang San Language 60
2 Zhang San, mathematics 65
3. three foreign languages 70
4. Li Si language 80
5 Li Si mathematics 90
6 Li Si Foreign Language 85
7. Wang Wu language 70
8. Wang Wu, mathematics 71
9 Wang Wu Foreign Language 75
10 Zhao Liu language 64
11 Zhao liumath 67
12 Zhao Liu Foreign Language 76

The query result is as follows:

Name Chinese Mathematics Foreign Language

Li Si 80 90 85
Wang Wu 70 71 75
Zhang San 60 65 70
Zhao Liu 64 67 76

Prepare data:

If exists (select id from sysobjects where xtype = 'U' and name = 'studentscore ')
Drop table studentscore -- delete a table that conflicts with the experiment
Go
Create table studentscore -- create an experiment table
(
[Id] int identity (1, 1 ),
[Name] nvarchar (20) not null,
Subject nvarchar (20) not null,
Score int not null
)
Go
-- Add lab data
Insert studentscore values ('zhang san', 'China', '60 ');
Insert studentscore values ('zhang san', 'mat', '65 ');
Insert studentscore values ('zhang san', '', '70 ');
Insert studentscore values ('Li si', 'China', '80 ');
Insert studentscore values ('Li si', 'mat', '90 ');
Insert studentscore values ('Li si', '', '85 ');
Insert studentscore values ('wang wu', 'China', '70 ');
Insert studentscore values ('wang wu', 'mat', '71 ');
Insert studentscore values ('wang wu', '', '75 ');
Insert studentscore values ('zhao liu', 'China', '64 ');
Insert studentscore values ('zhao liu', 'mat', '67 ');
Insert studentscore values ('zhao liu', '', '76 ');
Go
Select [id], [name], subject, score from studentscore
Go

1 Zhang San Language 60
2 Zhang San, mathematics 65
3. three foreign languages 70
4. Li Si language 80
5 Li Si mathematics 90
6 Li Si Foreign Language 85
7. Wang Wu language 70
8. Wang Wu, mathematics 71
9 Wang Wu Foreign Language 75
10 Zhao Liu language 64
11 Zhao liumath 67
12 Zhao Liu Foreign Language 76

Use the case when then else end statement to convert rows into columns:

Select [name], [language] = sum (case when subject = 'China' then score else null end ),
[Mathematics] = sum (case when subject = 'mate' then score else null end ),
[Foreign Language] = sum (case when subject = 'foreign language 'then score else null end)
From studentscore group by [name]

Query results:

Li Si 80 90 85
Wang Wu 70 71 75
Zhang San 60 65 70
Zhao Liu 64 67 76

The preceding queries are also very useful in many cases, such as fixed column headers in the product sales table based on quarterly statistics and month statistics, however, in most cases, the following headers are not fixed, such as City. Users may delete and add cities at any time. This is what we call Dynamic cross tabulation, in this case, you need to spell out the SQL statement.

Local variable Assignment Method in SQLServer
There are two types:
One type: set @ variable name = Value
Two types: select @ variable name = Value

Second, you can obtain data from a table and assign the value to the variable.
For example, the user name with the cid of 20 is assigned to the variable name from the user information table.
Declare @ name varchar (10) -- User name
Select @ name = userName from userInfo where cid = 20
Print 'cid 20 User name: '+ @ name

Recursive select variables:
Recursive select variables refer to the use of select statements and subqueries to concatenate a variable with itself. Syntax: select @ variable = @ variable + table. column from table

Declare @ SQL varchar (max)
Set @ SQL = 'select [name],'
Select @ SQL = @ SQL + 'sum (case subject when ''' + subject + '''
Then score else null end) as ''' + subject + ''','
From (select distinct subject from studentscore) as
Select @ SQL = left (@ SQL, len (@ SQL)-1) + 'from studentscore group by [name]'
Exec (@ SQL)

Execution result:

Lili 9085 80
Wang wu71 75 70
Zhang San 65 70 60
Zhao liu67 76 64