The concept of cross tabulation and the use of cross tabulation in SQL

A cross-tabulation is a common classified summary table. Use a cross-tabulation query to display the summary values of a field in the source table and group them, One group is listed on the left of the data table, and the other is listed on the top of the data table. . Data can be summarized and computed at the intersection of rows and columns. Such as sum, average, Count, maximum, and minimum. It is very intuitive and clear to use cross tabulation to query data and is widely used. Cross-table queries are also a feature of databases.

Create Table [test] (

[ID] [int] identity (1, 1) not null,

[Name] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Subject] [nvarchar] (50) Collate chinese_prc_ci_as null,

[Source] [numeric] (18, 0) null

) On [primary]

Go

Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', n '', 60)

Insert into [test] ([name], [subject], [Source]) values (N 'Li si', N 'mat', 70)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 80)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', N 'mat', 75)

Insert into [test] ([name], [subject], [Source]) values (N 'wang wu', n' ', 57)

Insert into [test] ([name], [subject], [Source]) values (n'li si', n' ', 80)

Insert into [test] ([name], [subject], [Source]) values (N 'zhang san', N 'angles', 100)

Go

Select * from test
ID name subject Source
1 Zhang San Language 60
2 Li Si mathematics 70
3 Wang Wu English 80
4 Wang Wu math 75
5. Wang Wu (Chinese) 57
6. Li Si language 80
7. James English 100

1. Confirm the column and query the crosstab chart
Select a. Name,
Sum (case subject when 'China' then source else 0 end) as language,
Sum (case subject when 'mate' then source else 0 end) as math,
Sum (case subject when 'then source else 0 end) as English
From test as
Group by A. Name
Reslut:
Name Chinese Mathematical English
Li Si 80 70 0
Wang Wu 57 75 80
Zhang San 60 0 100

2. The number of columns is unknown. You need to declare variables to obtain the subjects to be listed.

Declare @ SQL varchar (8000)

Set @ SQL = 'select name ,'

 

Select @ SQL = @ SQL + 'sum (case subject when ''' + Subject + '''

Then source else 0 end) as ''' + Subject + ''','

From (select distinct subject from test) as

 

Select @ SQL = left (@ SQL, Len (@ SQL)-1) + 'from test group by name'

Exec (@ SQL)

Go