列转行在多数据量(上万行数据)下的情况请问怎么办？

如题：if not object_id('tempdb..#t_Class') is null
drop table tempdb..#t_Class
Go
Create table #t_Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert #t_Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'英语',90 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go
select * from #t_Classgodeclare @s nvarchar(4000)
set @s=''
Select @s=@s+','+'sum(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'+quotename([Course])+''
from #t_Class group by[Course]
--set @s= SUBSTRING(@s,2,LEN(@s))
print @sexec('select [Student]'+@s+' from #t_Class group by [Student]')
在这种少数据量的情况下是没有问题。当我表的数据超过几W行的情况。变量@s 的长度不够.导致SQL语法错误。请问在这种情况下改如何改进上面的SQL.

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

nvarchar最大的长度只有4000吧。
2005用varchar(max)
如果是2000需要拼接变量了是相当麻烦的事情
转载: zjcxc(邹建)的专栏化解字符串不能超过8000的方法及交叉表的处理
经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题
下面就讨论这个问题: /*-- 数据测试环境 --*/
if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tb]
GO create table tb(单位名称 varchar(10),日期 datetime,销售额 int)
insert into tb
  select 'A单位','2001-01-01',100
  union all select 'B单位','2001-01-02',101
  union all select 'C单位','2001-01-03',102
  union all select 'D单位','2001-01-04',103
  union all select 'E单位','2001-01-05',104
  union all select 'F单位','2001-01-06',105
  union all select 'G单位','2001-01-07',106
  union all select 'H单位','2001-01-08',107
  union all select 'I单位','2001-01-09',108
  union all select 'J单位','2001-01-11',109 /*-- 要求结果
日期 A单位 B单位 C单位 D单位 E单位 F单位 G单位 H单位 I单位 J单位
---------- ----- ----- ----- ----- ----- ----- ---- ---- ---- ------
2001-01-01 100 0 0 0 0 0 0 0 0 0
2001-01-02 0 101 0 0 0 0 0 0 0 0
2001-01-03 0 0 102 0 0 0 0 0 0 0
2001-01-04 0 0 0 103 0 0 0 0 0 0
2001-01-05 0 0 0 0 104 0 0 0 0 0
2001-01-06 0 0 0 0 0 105 0 0 0 0
2001-01-07 0 0 0 0 0 0 106 0 0 0
2001-01-08 0 0 0 0 0 0 0 107 0 0
2001-01-09 0 0 0 0 0 0 0 0 108 0
2001-01-11 0 0 0 0 0 0 0 0 0 109
--*/ /*-- 常规处理方法*/
declare @sql varchar(8000)
set @sql='select 日期=convert(varchar(10),日期,120)'
select @sql=@sql+',['+单位名称
  +']=sum(case 单位名称 when '''+单位名称+''' then 销售额 else 0 end)'
from(select distinct 单位名称 from tb) a
exec(@sql+' from tb group by convert(varchar(10),日期,120)')
/*-- 问题: 如果单位很多,这时,@SQL的值就会被截断,从而出错.*/ /*--下面给出三种解决办法:--*/ --/*-- 方法1. 多个变量处理 --定义变量,估计需要多少个变量才能保存完所有数据
declare @sql0 varchar(8000),@sql1 varchar(8000)
--,...@sqln varchar(8000) --生成数据处理临时表
select id=identity(int,0,1),groupid=0
  ,值=',['+单位名称 +']=sum(case 单位名称 when '''
  +单位名称+''' then 销售额 else 0 end)'
into #temp from(select distinct 单位名称 from tb) a --分组临时表,判断慨最多多少个单位可以组合成一个不超过8000的字符串,这里取假设为5个
update #temp set groupid=id/5 --5为每组的单位个数 --生成SQL语句处理字符串
  --初始化
select @sql0=''
  ,@sql1=''
-- ...
-- ,@sqln   --得到处理字符串
select @sql0=@sql0+值 from #temp where groupid=0 --第一个变量
select @sql1=@sql1+值 from #temp where groupid=1 --第二个变量
--select @sqln=@sqln+值 from #temp where groupid=n --第n个变量 --查询
exec('select 日期=convert(varchar(10),日期,120)'
  +@sql0+@sql1
-- ...+@sqln
  +' from tb group by convert(varchar(10),日期,120)
') --删除临时表
drop table #temp /*
优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分
缺点:要自行估计处理的数据,估计不足就会出错
*/
--*/
--/*--方法2. bcp+isql --因为要用到bcp+isql,所以需要这些信息
declare @servername varchar(250),@username varchar(250),@pwd varchar(250)
select @servername='zj' --服务器名
  ,@username='' --用户名
  ,@pwd='' --密码 declare @tbname varchar(50),@sql varchar(8000) --创建数据处理临时表
set @tbname='[##temp_'+convert(varchar(40),newid())+']'
set @sql='create table '+@tbname+'(值 varchar(8000))
  insert into '+@tbname+' values(''create view '
  +stuff(@tbname,2,2,'')+' as
select 日期=convert(varchar(10),日期,120)'')'
exec(@sql) set @sql='insert into '+@tbname+'
select '',[''+单位名称+'']=sum(case 单位名称 when ''''''
  +单位名称+'''''' then 销售额 else 0 end)''
  from(select distinct 单位名称 from tb) a'
exec(@sql) set @sql='insert into '+@tbname+'
  values(''from tb group by convert(varchar(10),日期,120)'')'
exec(@sql) --生成创建视图的文件,注意使用了文件:c:\temp.txt
set @sql='bcp "'+@tbname+'" out "c:\temp.txt" /S"'
  +@servername+'" /U"'+@username+'" /P"'+@pwd+'" /c'
exec master..xp_cmdshell @sql --删除临时表
set @sql='drop table '+@tbname
exec(@sql) --调用isql生成数据处理视图
set @tbname=stuff(@tbname,2,2,'')
set @sql='isql /S"'+@servername
  +case @username when '' then '" /E' else '" /U"'+@username+'" /P"'+@pwd+'"' end
  +' /d"'+db_name()+'" /i"c:\temp.txt"' exec master..xp_cmdshell @sql --调用视图,显示处理结果
set @sql='select * from '+@tbname+'
  drop view '+@tbname
exec(@sql) /*
优点:程序自动处理,不存在判断错误的问题
缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限
*/
--*/
--/*-- 方法3. 多个变量处理,综合了方法1及方法2的优点, 解决了方法1中需要人为判断的问题,自动根据要处理的数据量进行变量定义,同时又避免了方法2的繁琐 declare @sqlhead varchar(8000),@sqlend varchar(8000)
  ,@sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(8000)
  ,@i int,@ic varchar(20) --生成数据处理临时表
select id=identity(int,0,1),gid=0
  ,a=',['+单位名称 +']=sum(case 单位名称 when '''
  +单位名称+''' then 销售额 else 0 end)'
into # from(select distinct 单位名称 from tb) a --判断需要多少个变量来处理
select @i=max(len(a)) from #
print @i
set @i=7800/@i --分组临时表
update # set gid=id/@i
select @i=max(gid) from # --生成数据处理语句
select @sqlhead='''select 日期=convert(varchar(10),日期,120)'''
  ,@sqlend=''' from tb group by convert(varchar(10),日期,120)'''
  ,@sql1='',@sql2='select ',@sql3='',@sql4='' while @i> =0
  select @ic=cast(@i as varchar),@i=@i-1
  ,@sql1='@'+@ic+' varchar(8000),'+@sql1
  ,@sql2=@sql2+'@'+@ic+'='''','
  ,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
  +char(13)+@sql3
  ,@sql4=@sql4+',@'+@ic select @sql1='declare '+left(@sql1,len(@sql1)-1)+char(13)
  ,@sql2=left(@sql2,len(@sql2)-1)+char(13)
  ,@sql3=left(@sql3,len(@sql3)-1)
  ,@sql4=substring(@sql4,2,8000) --执行
exec( @sql1+@sql2+@sql3+'
exec('+@sqlhead+'+'+@sql4+'+'+@sqlend+')'
) --删除临时表
drop table #
--*/
方法3中,关键要做修改的是下面两句,其他基本上不用做改变: --生成数据处理临时表,修改a=后面的内容为相应的处理语句
select id=identity(int,0,1),gid=0
  ,a=',['+code+']=sum(case b.c_code when '''
  +code+''' then b.value else 0 end)'
into # from #Class --生成数据处理语句,将@sqlhead,@sqlend赋值为相应的处理语句头和尾
select @sqlhead='''select a.id,a.name,a.code'''
  ,@sqlend=''' from #Depart a,#Value b where a.Code=b.d_Code group by a.id,a.code,a.name'''
  ,@sql1='',@sql2='select ',@sql3='',@sql4=''
sql2000的话最大是8000
sql2005使用nvarchar(max)就行了
sql 2005 不是增加了unpivot么，可以用一下
如果用nvarchar(4000)的话还是不行。我才近3W的数据。不但要执行很长时间。最后还是没有显示完。用加勒比小飞猪的第三种方法还是可以的。可是我不需要日期这个字段。恕小弟愚昧，不知如果修改。还请大家赐教.select @sqlhead=''  --''select 日期=convert(varchar(10),日期,120)''
  ,@sqlend=''--' from #tb group by convert(varchar(10),日期,120)'''
  ,@sql1='',@sql2='select ',@sql3='',@sql4=''
修正上面所说的  nvarchar(4000)  改为 nvarchar(max)
建议先查询出有多少种Course,然后用静态SQL吧.
Create table #t_Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)
Insert #t_Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'英语',90 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Goselect Student,
       max(case Course when '语文' then Score else 0 end) [语文],
       max(case Course when '数学' then Score else 0 end) [数学],
       max(case Course when '英语' then Score else 0 end) [英语],
       max(case Course when '物理' then Score else 0 end) [物理]
from #t_Class
group by Studentdrop table #t_Class/*
Student 语文          数学          英语          物理
------- ----------- ----------- ----------- -----------
李四      65          77          65          85
张三      78          87          90          90（所影响的行数为 2 行）
*/

列转行在多数据量(上万行数据)下的情况 请问怎么办？

解决方案 »

列转行在多数据量(上万行数据)下的情况请问怎么办？