行转列 字符串超过8000解决思路

    declare @sql varchar(8000)    set @sql = 'select SiteName , MacSerialNumber '    select @sql = @sql + ' , max(case [name] when ''' + [name] + ''' then ThresholdValue else 0 end) [' + [name] + ']'    from (select distinct [name] from #tempSensorLabel) as a    set @sql = @sql + ' from #tempSensorLabel group by SiteName , MacSerialNumber'    PRINT(@sql)    exec(@sql) 

/*--化解字符串不能超过8000的方法    经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题    下面就讨论这个问题:--邹建 2003.9(引用请保留此信息)--*//*-- 测试环境--以系统表 syscolumns 为测试数据,要求按xtype为列字段,name为行字段,统计colid的和--要求结果xtype    filedname_1    fieldname_2    .....    fieldname_n-------- -------------- -------------- -------- --------------34       0              0              .....    1--*//*--常规处理方法(不加行数限制会因生成的字符串益出而出错)set rowcount 10    --因为syscolumns的记录较,会导致生成的字符串溢出,所以限制一下行数declare @s nvarchar(4000)set @s=''select @[email protected]+N','+quotename([name])    +N'=sum(case name when '+quotename([name],'''')    +N' then [colid] else 0 end)'from(select distinct [name] from [syscolumns]) aset rowcount 0    --取消限制exec(N'select [xtype][email protected]+N' from [syscolumns] group by [xtype]')/*--问题    不加行数限制时,会因生成的字符串益出而出错--*/--*//*--方法1. 多个变量处理--根据查询结果定义变量(实际处理中,应该是估计需要多少个变量,定义足够多的变量个数,多定义变量并不影响处理,下面就多定义了一个)--生成数据处理临时表SELECT id=IDENTITY(int,0,1),    g=0,    a=CAST(N','    +QUOTENAME([name])        +N'=SUM(CASE [name] WHEN N'    +QUOTENAME(name,N'''')        +N' THEN [colid] ELSE 0 END)'        as nvarchar(4000))INTO # FROM syscolumnsWHERE name>N''GROUP BY name--分组临时表UPDATE a SET G=id/iFROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)bSELECT MAX(g)+1 as N'需要的变量个数' FROM #DECLARE @0 nvarchar(4000),@1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(4000)SELECT @0=N'',@1=N'',@2=N'',@3=N'',@4=N''SELECT     @0=CASE g WHEN 0 THEN @0+a ELSE @0 END,    @1=CASE g WHEN 1 THEN @1+a ELSE @1 END,    @2=CASE g WHEN 2 THEN @2+a ELSE @2 END,    @3=CASE g WHEN 3 THEN @3+a ELSE @3 END,    @4=CASE g WHEN 4 THEN @4+a ELSE @4 ENDFROM #EXEC(N'SELECT [email protected][email protected][email protected][email protected][email protected]+N' FROM syscolumns GROUP BY xtype')DROP TABLE #/*--方法说明优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分缺点:要自行估计处理的数据,估计不足就会出错--*/--*//*--方法2. bcp+isql--因为要用到bcp+isql,所以需要这些信息declare @servername sysname,@username sysname,@pwd sysnameselect @servername=@@servername  --服务器名    ,@username=N''           --用户名    ,@pwd=N''                --密码declare @tbname sysname,@s nvarchar(4000)--创建数据处理临时表set @tbname=quotename(N'##temp_'+cast(newid() as varchar(36)))set @s=N'create table [email protected]+'(a nvarchar(4000))insert into [email protected]+N'select N''create view '    +stuff(@tbname,2,2,N'')    +N' as select [xtype]''union allselect N'',''+quotename([name])+''=sum(case [name] when N''    +quotename([name],'''''''')    +'' then [colid] else 0 end)''    from(select distinct [name] from [syscolumns] where name<>N''xtype'')aunion allselect N''from [syscolumns] group by [xtype]'''exec(@s)--生成创建视图的文件,注意使用了文件:c:\temp.txtset @s=N'bcp "[email protected]+N'" out "c:\[email protected]+N'" /S"'    [email protected]+N'" /U"[email protected]+N'" /P"[email protected]+N'" /w'exec master..xp_cmdshell @s,no_output--调用isql生成数据处理视图set @s=N'osql /S"[email protected]    +case         when @username=N'' then N'" /E'         else N'" /U"[email protected]+N'" /P"[email protected]+N'"'    end    +N' /d"'+db_name()+N'" /i"c:\[email protected]+'"'exec master..xp_cmdshell @s,no_output--删除临时文件set @s=N'del "c:\[email protected]+'"'exec master..xp_cmdshell @s,no_output--调用视图,显示处理结果set @s=N'drop table [email protected]+N'select * from '+stuff(@tbname,2,2,N'')+N'drop view '+stuff(@tbname,2,2,N'')exec(@s)
------解决方案--------------------