SQL cross tabulation instance

SQL cross tabulation instance

It's a simple thing. Many people on the internet asked "How to implement cross tabulation ?", The following is an example I wrote. The database is based on SQL Server 2000.

-- ===================================================== ====================

-- Cross tabulation instance

-- ===================================================== ====================

Table creation:

Run in the query Analyzer:

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

 

Implementation of cross tabulation statements:

-- Used: the number of columns in the crosstab chart is determined.

Select name, sum (case subject when 'mate' then source else 0 end) as 'mate ',

Sum (case subject when 'then source else 0 end) as 'hangzhou ',

Sum (case subject when 'chine' then source else 0 end) as 'chine'

From Test

Group by name

 

 

-- Used: the number of columns in the crosstab chart is uncertain.

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

 

Running result: