SQL column-to-row

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

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

Declare @ SQL varchar (8000)

Set @ SQL = 'select id ,'

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

Then source else null end) as ''' + subject + ''','

From (select distinct subject from test) as

Select @ SQL = left (@ SQL, len (@ SQL)-1) + 'from Test'

Exec (@ SQL)

Go