How do I write the results of the execution of exec (@sql) into the table (temporary tables can also be)

xbl916

Vertical to horizontal.
There are tables: test (name char (10), km char (10), cj int)
name km cj
----------------------------------------------
Zhang San Chinese 80
Zhang San Mathematics 86
Zhang San English 75
Lee Si Language 78
Lee Si Mathematics 85
Lee Si English 78

Requires display in landscape format, that is:
Want to become

Name Language Mathematics English
----------------------------
Zhang San 80 86 75
Lee Si 78 85 78

Create table test (name char (10), km char (10), cj int)
go
insert test values ​​('Zhang San', 'Language', 80)
insert test values ​​('Zhang San', 'Mathematics', 86)
insert test values ​​('Zhang San', 'English', 75)
insert test values ​​('Li Si', 'Chinese', 78)
insert test values ​​('Li Si', 'Mathematics', 85)
insert test values ​​('Li Si', 'English', 78)
method:
declare @sql varchar (8000)
set @sql = 'select name'
select @sql = @sql + ', sum (case km when' '' + km + '' 'then cj end) [' + km + ']'
 from (select distinct km from test) as a
select @sql = @ sql + 'from test group by name'
exec (@sql)

How do I write the results of the execution of exec (@sql) into the table (temporary tables can also be)

sinexe

insert into #Temporary table
exec (@sql)

fanfan1980

--try

declare @sql varchar (8000)
set @sql = 'select name'
select @sql = @sql + ', sum (case km when' '' + km + '' 'then cj end) [' + km + ']'
 from (select distinct km from test) as a
select @sql = @ sql + 'into #T from test group by name'
exec (@sql)

select * from #T

fanfan1980

--Is it possible to use global temporary tables? Partial temporary table does not work

declare @sql varchar (8000)
set @sql = 'select name'
select @sql = @sql + ', sum (case km when' '' + km + '' 'then cj end) [' + km + ']'
 from (select distinct km from test) as a
select @sql = @ sql + 'into ## T from test group by name'
exec (@sql)

select * from ## T

sinexe

insert into #Temporary table
exec (@sql)
＝＝＝＝＝＝＝＝
Write one less sentence
create table #Temporary table (...)