好贷网好贷款

行转列问题中如要转的列非常多 用declare中定义的 变量最多存储8000个字符,但是我的数据非常多 定义变量装不下 怎么办 还有其他办法吗? 50rrkxhy]

发布时间:2016-12-3 3:56:49 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"行转列问题中如要转的列非常多 用declare中定义的 变量最多存储8000个字符,但是我的数据非常多 定义变量装不下 怎么办 还有其他办法吗? 50rrkxhy]",主要涉及到行转列问题中如要转的列非常多 用declare中定义的 变量最多存储8000个字符,但是我的数据非常多 定义变量装不下 怎么办 还有其他办法吗? 50rrkxhy]方面的内容,对于行转列问题中如要转的列非常多 用declare中定义的 变量最多存储8000个字符,但是我的数据非常多 定义变量装不下 怎么办 还有其他办法吗? 50rrkxhy]感兴趣的同学可以参考一下。

行转列问题 表1如下     A            B 200901   F23,F34,F56,T67 200902   F23,T34,T67,T89 200903   TP25,TP45,F45,F56 表2如下   A     F23 F34 F56 T67 T89 TP25 TP45 F45    200901   1   1   1   1   0   0    0    0 200902   1   1   0   1   1   0    0    0 200903   0   0   1   0   0   1    1    1    意思把表1变为表2. 表1解释如下:字段A里的数据是读者的借书号,B是读者所借的书籍的索引号。 现在要变为表2 意思是:把所借的书籍的索引号改为列名,读者借了哪本书 其列数值为1 否则为0   就是布尔数值类型 不知道明白了没有? 有懂的加QQ 443714055 谢谢 且我已经用如下代码完成了部分行转列的问题 declare @sql varchar(8000) set @sql=' select code ' select @[email protected]+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']'  from (select distinct indexcode from t1) as aaa set @[email protected]+' from t1 group by code' print @sql exec (@sql) 这个语句可以完成一些 但是 我要转的列如果是有 1000个 行是1000的话 那么总大小就是 1000000  [email protected] 没存储那么多 在执行时 只有0行受 影响  谁能还有其他语句可以解决问题 非常感谢  如有问题不懂的 可以加 443714055 详问……谢谢  

分两个变量去存储了!最后执行的时候执行 exec(@[email protected]+...)

能给个示例吗 谢谢   给你个代码  你帮把他用你的方法写下来 试试 谢谢 create table t1 (     code varchar(10),     indexcode varchar(10) ) insert into t1 select '200901', 'F23' union all select '200901', 'F34' union all select '200901', 'F56' union all select '200901', 'T67' union all select '200902', 'F23' union all select '200902', 'F34' union all select '200902', 'T67' union all select '200902', 'T89' union all select '200903', 'F56' union all select '200903', 'F45' union all select '200903', 'TP25' union all select '200903', 'TP45' select * from t1 declare @sql varchar(8000) set @sql=' select code ' select @[email protected]+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']'  from (select distinct indexcode from t1) as aaa set @[email protected]+' from t1 group by code' print @sql exec (@sql)

sql server 2000 ?

我用的是SQL2008  上面代码也是从那上面运行了的 是做了个简单的测试 别人给发的

create table t1 (   code varchar(10),   indexcode varchar(10) ) insert into t1 select '200901', 'F23' union all select '200901', 'F34' union all select '200901', 'F56' union all select '200901', 'T67' union all select '200902', 'F23' union all select '200902', 'F34' union all select '200902', 'T67' union all select '200902', 'T89' union all select '200903', 'F56' union all select '200903', 'F45' union all select '200903', 'TP25' union all select '200903', 'TP45' go select distinct indexcode,rid=row_number() over (order by getdate()) into #tb from t1 declare @sql varchar(8000)  --第一个拼接字符串 declare @str varchar(8000)  --第二个拼接字符串 set @sql=' select code ' select @[email protected]+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']'   from #tb where rid between 1 and 6 select @str = isnull(@str,'')+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']' from #tb where rid between 7 and 12 exec (@[email protected]+' from t1 group by code') drop table t1,#tb /************ code       F23         F23         F34         F34         F56         T67         F45         F56         T67         T89         TP25        TP45 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 200901     1           1           1           1           1           1           0           1           1           0           0           0 200902     1           1           1           1           0           1           0           0           1           1           0           0 200903     0           0           0           0           1           0           1           1           0           0           1           1 (3 行受影响)

引用 4 楼 rrkxhy 的回复:我用的是SQL2008  上面代码也是从那上面运行了的 是做了个简单的测试 别人给发的 sql2008就用declare @sql varchar(max)了

引用 6 楼 perfectaction 的回复:引用 4 楼 rrkxhy 的回复: 我用的是SQL2008  上面代码也是从那上面运行了的 是做了个简单的测试 别人给发的 sql2008就用declare @sql varchar(max)了 忘了。

exec(@[email protected]) --拼接两个8000的字符串就行了

/*--化解字符串不能超过8000的方法  经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题  下面就讨论这个问题:  --邹建   2003.9(引用请保留此信息)--*/  --方法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   syscolumns  WHERE   name> N ' '  GROUP   BY   name  --分组临时表  UPDATE   a   SET   G=id/i  FROM   #   a,(SELECT   i=3800/MAX(LEN(a))   FROM   #)b  SELECT   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   END  FROM   #  EXEC(N 'SELECT   xtype [email protected][email protected][email protected][email protected][email protected]+N '   FROM   syscolumns   GROUP   BY   xtype ')  DROP   TABLE   #  /*--方法说明  优点:比较灵活,数据量大时只需要增加变量就行了.不用改动其他部分  缺点:要自行估计处理的数据,估计不足就会出错  --*/  /*--化解字符串不能超过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]) a set 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 syscolumns WHERE name>N'' GROUP BY name --分组临时表 UPDATE a SET G=id/i FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b SELECT 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 END FROM # 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 sysname select @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 all select 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'')a union all select N''from [syscolumns] group by [xtype]''' exec(@s) --生成创建视图的文件,注意使用了文件:c:\temp.txt set @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) /*--方法总结     优点:程序自动处理,不存在判断错误的问题     缺点:复杂,经过的步骤多,容易出错,而且需要一定的操作员权限 --*/ --*/ --/*-- 方法3. 多个变量处理,综合了方法1,2的优点,解决了方法1中需要人为判断,增加变量的问题,排除了方法2,需要权限和过程复杂的问题 DECLARE @sqlhead nvarchar(4000),@sqlend nvarchar(4000)     ,@sql1 nvarchar(4000),@sql2 nvarchar(4000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)     ,@i int,@ic nvarchar(10) --生成数据处理临时表 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(     SELECT DISTINCT name FROM [syscolumns] WHERE name>N'')a --分组临时表 UPDATE a SET @i=id/i,[email protected] FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b SET @[email protected] --生成数据处理语句 SELECT      @sqlhead=N''''         +REPLACE(N'SELECT [xtype]',N'''',N'''''')         +'''',     @sqlend=N''''         +REPLACE(N' FROM [syscolumns] GROUP BY [xtype]',N'''',N'''''')         +N'''',     @sql1=N'',@sql2=N'',@sql3=N'',@sql4=N'' WHILE @ic>=0     SELECT          @sql1=N',@[email protected]+N' nvarchar(4000)[email protected],         @sql2=N',@[email protected][email protected],         @sql3=N',@[email protected]             +N'=CASE g WHEN [email protected]             +N' THEN @[email protected]+N'+a ELSE @[email protected]             +N' [email protected],         @sql4=N'+@[email protected][email protected],         @[email protected] SELECT      @sql1=STUFF(@sql1,1,1,N''),     @sql2=STUFF(@sql2,1,1,N''),     @sql3=STUFF(@sql3,1,1,N''),     @sql4=STUFF(@sql4,1,1,N'') --执行 EXEC(N'DECLARE [email protected]+N' SELECT [email protected]+N' SELECT [email protected]+N' FROM # EXEC([email protected][email protected][email protected]+N')') --删除临时表 DROP TABLE # /*--方法总结          总结了前两种方法的优点,自动判断需要处理的变量数 --*/ --*/

引用 5 楼 acherat 的回复:SQL code create table t1 (   code varchar(10),   indexcode varchar(10) ) insert into t1 select '200901', 'F23' union all select '200901', 'F34' union all select '200901', 'F56' union all …… /************ code       F23         F23         F34         F34         F56         T67         F45         F56         T67         T89         TP25        TP45 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 200901     1           1           1           1           1           1           0           1           1           0           0           0 200902     1           1           1           1           0           1           0           0           1           1           0           0 200903     0           0           0           0           1           0           1           1           0           0           1           1 (3 行受影响) 你得出的列有重复啊  看见没前那几个列都重复着呢 能不能不 重复哟 我的数据量大 可不可以多弄几个 变量啊  我都不会改……不太懂呀   麻烦能不能写个没有重复且多分几个变量的 谢谢啦

declare @sql varchar(8000) 改成 declare @sql varchar(max) 不行吗?

max 最大的意思就是 8000个字符了。这两句是一个意思

引用 12 楼 rrkxhy 的回复:max 最大的意思就是 8000个字符了。这两句是一个意思 不是一个意思,max可以达到2G.

create table t1 (   code varchar(10),   indexcode varchar(10) ) insert into t1 select '200901', 'F23' union all select '200901', 'F34' union all select '200901', 'F56' union all select '200901', 'T67' union all select '200902', 'F23' union all select '200902', 'F34' union all select '200902', 'T67' union all select '200902', 'T89' union all select '200903', 'F56' union all select '200903', 'F45' union all select '200903', 'TP25' union all select '200903', 'TP45' go select distinct indexcode,rid=identity(int,1,1)     into #tb from t1 declare @sql varchar(8000)  --第一个拼接字符串 declare @str varchar(8000)  --第二个拼接字符串 set @sql=' select code ' select @[email protected]+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']'   from #tb where rid between 1 and 4 select @str = isnull(@str,'')+' ,max(case when indexcode='''+indexcode+''' then 1 else 0 end) as ['+indexcode+']' from #tb where rid between 5 and 8 exec (@[email protected]+' from t1 group by code') drop table t1,#tb /*************** code       F23         F34         F45         F56         T67         T89         TP25        TP45 ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 200901     1           1           0           1           1           0           0           0 200902     1           1           0           0           1           1           0           0 200903     0           0           1           1           0           0           1           1 (3 行受影响)

引用 14 楼 acherat 的回复:SQL code create table t1 (   code varchar(10),   indexcode varchar(10) ) insert into t1 select '200901', 'F23' union all select '200901', 'F34' union all select '200901', 'F56' union all …… 你好谢谢你的回答  我为什么在用时都会有重复呢? 就是 我有一个表 结构为   (读者号  细类) 里面的数据总共1356行纪录 对表进行 (distinct 细类) 后 779行 也就是说 细类不重复是 779种 然后把每种细类都做为一个列  就是上面说的那样 我在用那些变量时 他就会有重复   是不是在选行数时 要根据变量能存储的数据量而行  我的语句是这样的 select rid=identity(int,1,1) distinct 细类 from rrk.dbo.xil2007y  declare @sql varchar(8000)  declare @str varchar(8000) declare @str1 varchar(8000) declare @str2 varchar(8000)  declare @str3 varchar(8000)  declare @str4 varchar(8000)  declare @str5 varchar(8000)  declare @str6 varchar(8000)  declare @str7 varchar(8000)  declare @str8 varchar(8000)  declare @str9 varchar(8000)  declare @str10 varchar(8000)  declare @str11 varchar(8000)  declare @str12 varchar(8000)  declare @str13 varchar(8000) set @sql=' select 读者号 ' select @[email protected]+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']'   from rrk.dbo.bb  where rid between 1 and 100 select @str = isnull(@str,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 101 and 150 select @str1 = isnull(@str1,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 151 and 200 select @str2 = isnull(@str2,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 201 and 250 select @str3 = isnull(@str3,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 251 and 300 select @str4 = isnull(@str4,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 301 and 350 select @str5 = isnull(@str5,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 351 and 400 select @str6 = isnull(@str6,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 401 and 450 select @str7 = isnull(@str7,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 451 and 500 select @str8 = isnull(@str8,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 501 and 550 select @str9 = isnull(@str9,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 551 and 600 select @str10 = isnull(@str10,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 601 and 650 select @str11 = isnull(@str11,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 651 and 700 select @str12 = isnull(@str12,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 701 and 750 select @str13 = isnull(@str13,'')+' ,max(case when 细类='''+细类+''' then 1 else 0 end) as ['+细类+']' from rrk.dbo.bb where rid between 751 and 779 exec (@[email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected][email protected]+' from rrk.dbo.xil2007y group by 读者号') 其中在选择between 1到100时 前面的一些数据就会重复  经过发现 在选到 1到74时 就不会重复 但是 按这样的计算  我依次写下去 还是发现有些重复 那是为什么呀 能加你QQ了解吗 谢谢  443714055   

引用 13 楼 perfectaction 的回复:引用 12 楼 rrkxhy 的回复: max 最大的意思就是 8000个字符了。这两句是一个意思 不是一个意思,max可以达到2G. 那我就不知道了 我反正是有一个表 里面有 1356行数据  也就三列 我想行转列 但是 要转为行的那个列的数据少的话 用一个变量转就可以 但是 我的那要转的 有的很多  779种 转过来的话就 779行了 那样 老是运行不出来 我就不清楚 为什么了

上一篇:SQLServer2008报表 20zhengjf123]
下一篇:求sql server 的编码联想工具 20pch_zhang]

相关文章

相关评论