22,207
社区成员
发帖
与我相关
我的任务
分享
USE
tempdb --环境为 Microsoft SQL Server 2014
--测试数据 检查数据 -- select* from #T
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([月份] int,[成绩] int,[班级] char(2))
Insert #T
select N'1',N'190','01' union all
select N'2',N'160','02' union all
select N'3',N'150','03' union all
select N'4',N'140','04' union all
select N'5',N'110','05' union all
select N'6',N'100','06' union all
select N'7',N'99','07' union all
select N'8',N'90','08'
Go
--如何用动态语句完成以下的转换
月份 成绩 班级
1 190 01
2 160 02
3 150 03
4 140 04
5 110 05
6 100 06
7 99 07
8 90 08
班级 01 02 03 04 05 06 07 08
成绩 190 160 150 140 110 100 99 90
月份 1 2 3 4 5 6 7 8
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(号码) from #temp where 统计次数 between 11 and 18 group by 号码,统计次数 ORDER BY 统计次数
set @sql=N'
select * into ##fushi from (
select t.号码 as class,c.* from #temp as t
cross apply(values(N''统计次数'',统计次数),(N''出现的号码'',出现的号码)) c(号码,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p select * from ##fushi'
exec (@sql)
--select * from ##fushi
[/quote]
你后面写的目的是啥?如果仅返回记录集,最初的语句就行,不需要用临时表。
因为考虑你可能需要在动态语句执行完后任然需要刚才得到结果,才需要将结果放入临时表。
当然如果你既想EXEC语句执行返回记录集,也想在执行完的后续步骤仍然能使用执行结果,就可以这么写
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(号码) from #temp where 统计次数 between 11 and 18 group by 号码,统计次数 ORDER BY 统计次数
set @sql=N'
select * into ##fushi from (
select t.号码 as class,c.* from #temp as t
cross apply(values(N''统计次数'',统计次数),(N''出现的号码'',出现的号码)) c(号码,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p select * from ##fushi'
exec (@sql)
--select * from ##fushi
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(班级) from #t group by 班级
set @sql=N'
select * into ##tt from (
select t.班级 as class,c.* from #t as t
cross apply(values(N''成绩'',成绩),(N''月份'',月份)) c(班级,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p'
exec (@sql)
select * from ##tt
[/quote][/quote]
我写的语句漏掉了into
IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
drop table #tmp
select * into #tmp from #t where 1=2
declare @sql nvarchar(max)
set @sql='select * from #t '
into #tmp
exec (@sql)
[/quote]
因为你这里是动态语句,所以返回的结果不是固定的。
我看写的
select * into #tmp from #t where 1=2
这句应该为了创建临时表,那么这里的#t哪来了
如果想将动态语句结果放入临时表,你可以将INTO集成动态语句中
但这时不能用临时表,在EXEC命令内部创建的临时表,在外表不能用,需要用全局临时表或数据表
全局临时表是前面两个##,一般不建议用,因为它和数据表差不多,别的transcation也可以访问到
比如:
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(班级) from #t group by 班级
set @sql=N'
select * ##tt from (
select t.班级 as class,c.* from #t as t
cross apply(values(N''成绩'',成绩),(N''月份'',月份)) c(班级,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p'
exec (@sql)
select * from ##tt
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(班级) from #t group by 班级
set @sql=N'
select * into ##tt from (
select t.班级 as class,c.* from #t as t
cross apply(values(N''成绩'',成绩),(N''月份'',月份)) c(班级,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p'
exec (@sql)
select * from ##tt
[/quote]
IF object_id('[tempdb].[dbo].#tmp') IS NOT NULL --判断临时表#tmp是否存在,存在则删除
drop table #tmp
select * into #tmp from #t where 1=2
declare @sql nvarchar(max)
set @sql='select * from #t '
into #tmp
exec (@sql)
declare @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+quotename(班级) from #t group by 班级
set @sql=N'
select * from (
select t.班级 as class,c.* from #t as t
cross apply(values(N''成绩'',成绩),(N''月份'',月份)) c(班级,item)
) as t pivot(sum(item) for class in ('+@cols+N')) p'
exec (@sql)
班级 01 02 03 04 05 06 07 08
1 成绩 190 160 150 140 110 100 99 90
2 月份 1 2 3 4 5 6 7 8
USE
tempdb --环境为 Microsoft SQL Server 2014
--测试数据 检查数据 -- select* from #T
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([月份] int,[成绩] int,[班级] char(2))
Insert #T
select N'1',N'190','01' union all
select N'2',N'160','02' union all
select N'3',N'150','03' union all
select N'4',N'140','04' union all
select N'5',N'110','05' union all
select N'6',N'100','06' union all
select N'7',N'99','07' union all
select N'8',N'90','08'
Go
--如何用动态语句完成以下的转换
DECLARE @s NVARCHAR(4000) ,
@s2 NVARCHAR(4000) ,
@s3 NVARCHAR(4000) ,
@s4 NVARCHAR(4000)
SELECT @s = ISNULL(@s + ',', 'declare ') + '@' + RTRIM(Colid)
+ ' nvarchar(4000)' ,
@s2 = ISNULL(@s2 + ',', 'select ') + '@' + RTRIM(Colid) + '='''
+ CASE WHEN @s2 IS NOT NULL THEN 'union all select'
ELSE ' select '
END + ' a0=''' + QUOTENAME(Name, '''') + '''''' ,
@s3 = ISNULL(@s3, '') + 'select @' + RTRIM(Colid) + '=@' + RTRIM(Colid)
+ '+'',''+quotename([月份])+''=''+quotename(' + QUOTENAME(Name)
+ ','''''''') from #T ' ,
@s4 = ISNULL(@s4 + '+', '') + '@' + RTRIM(Colid)
FROM syscolumns
WHERE id = OBJECT_ID('#T')
EXEC(@s+' '+@s2+' '+@s3+' exec('+@s4+')')