严重同意 tyd163xx的方法,不过要想程序好用,还是在前端处理多列的好一般后端进行分组group by ,返回小结果集,前端再展开成多列,这样速度就没有问题了
暂时以另一种方式解决:横向显示 地区(dqdm), 纵向显示 通路(tlmc)..所幸 地区 分类在50个以下,所以 @sql 字符串将在 2000 以下, 不会超出限制, 存储过程得以顺利执行..另外, 地区 分类的增加比较缓慢..所以几年内应不会出现错误吧..:) 现在的存储过程修改如下:CREATE PROCEDURE dbo.u_dt_tl_msdqfbtj --通路门市地区分布统计 /* 通路 TB TBC.....TZN 合 计 好又多 T1 T2 T12 TT 家乐 C1 C2 Cn TT . . . . . . . . . . . . . . .*/ AS SELECT msdm, RTRIM(lsdq) AS dqdm, RTRIM(tlmc) AS tlmc INTO #t FROM hcszhmc WHERE tlmc IS NOT NULL --通路门市地区分布统计 ---------------------------------------------------------------------------------- --/* DECLARE @SQL VARCHAR(8000) DECLARE @tsql VARCHAR(8000) SET @sql = 'select tlmc as ''通路名称''' --生成动态列 SELECT @sql = @sql + ', SUM(CASE dqdm WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + RTRIM(CAST(dqdm AS NVARCHAR(30))) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''' GROUP BY tlmc' SET @tsql = ' UNION ALL SELECT ''合计''' --生成动态列 SELECT @tsql = @tsql + ', SUM(CASE DQDM WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(dqdm AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''''
用这个吧,比较通用DECLARE @SQL1 VARCHAR(8000),@sql2 varchar(8000) --定义足够多的变量 declare @tsql varchar(8000)declare @fdcount int set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储--生成动态列 --生成数据处理临时表 select id=identity(int,0,1),0 as groupid,0 as sortid,* into #tb from(select DISTINCT TLMC FROM #t) a--对要处理的字段分组 update #tb set groupid=id/@fdcount,sortid=id % @fdcount--组合字符串 select @fdcount=@fdcount-1 ,@sql1='' ,@sql2='' --如果有更多的变量,则继续写下去while @fdcount>=0 begin select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']'+@sql FROM #tb where sortid=@fdcount and groupid=0 select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']'+@sq2 FROM #tb where sortid=@fdcount and groupid=1 --如果有更多的变量,继续写下去 set @fdcount=@fdcount-1 end--生成动态列 SET @tsql = ' UNION SELECT ''合计''' SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'--查询结果 exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去 +', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM' +@tsql)
DECLARE @SQL1 VARCHAR(8000),@sql2 varchar(8000) --定义足够多的变量 declare @tsql varchar(8000)declare @fdcount int set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储--生成动态列 --生成数据处理临时表 select id=identity(int,0,1),0 as groupid,0 as sortid,* into #tb from(select DISTINCT TLMC FROM #t) a--对要处理的字段分组 update #tb set groupid=id/@fdcount,sortid=id % @fdcount--组合字符串 select @fdcount=@fdcount-1 ,@sql1='' ,@sql2='' --如果有更多的变量,则继续写下去while @fdcount>=0 begin select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']'+@sql FROM #tb where sortid=@fdcount and groupid=0 select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']'+@sq2 FROM #tb where sortid=@fdcount and groupid=1 --如果有更多的变量,继续写下去 set @fdcount=@fdcount-1 end--生成动态列 SET @tsql = ' UNION SELECT ''合计''' SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'--查询结果 exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去 +', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM' +@tsql)
现在的存储过程修改如下:CREATE PROCEDURE dbo.u_dt_tl_msdqfbtj
--通路门市地区分布统计
/*
通路 TB TBC.....TZN 合 计
好又多 T1 T2 T12 TT
家乐 C1 C2 Cn TT
. . . . .
. . . . .
. . . . .*/
AS
SELECT msdm, RTRIM(lsdq) AS dqdm, RTRIM(tlmc) AS tlmc INTO #t FROM hcszhmc WHERE tlmc IS NOT NULL --通路门市地区分布统计
----------------------------------------------------------------------------------
--/*
DECLARE @SQL VARCHAR(8000)
DECLARE @tsql VARCHAR(8000)
SET @sql = 'select tlmc as ''通路名称'''
--生成动态列
SELECT @sql = @sql + ', SUM(CASE dqdm WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + RTRIM(CAST(dqdm AS NVARCHAR(30))) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''' GROUP BY tlmc'
SET @tsql = ' UNION ALL SELECT ''合计'''
--生成动态列
SELECT @tsql = @tsql + ', SUM(CASE DQDM WHEN ''' + CAST(dqdm AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(dqdm AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT dqdm FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t WHERE tlmc IS NOT NULL AND tlmc<>'''''
SET @sql = @sql + @tsql
EXEC(@sql)
--*/
----------------------------------------------------------------------------------go
谢谢楼上朋友的回复!1.sp_executesql 我还没有用过, 所以, 我不知从何下手..2.tyd163xx(答案) 和 perwang(阿吉) 的意见不错..我也有想过, 只是我现在对游标也不太熟,可以给个较详细的例子吗?
declare @tsql varchar(8000)declare @fdcount int
set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储--生成动态列
--生成数据处理临时表
select id=identity(int,0,1),0 as groupid,0 as sortid,*
into #tb from(select DISTINCT TLMC FROM #t) a--对要处理的字段分组
update #tb set groupid=id/@fdcount,sortid=id % @fdcount--组合字符串
select @fdcount=@fdcount-1
,@sql1=''
,@sql2='' --如果有更多的变量,则继续写下去while @fdcount>=0
begin
select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sql
FROM #tb where sortid=@fdcount and groupid=0 select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sq2
FROM #tb where sortid=@fdcount and groupid=1
--如果有更多的变量,继续写下去 set @fdcount=@fdcount-1
end--生成动态列
SET @tsql = ' UNION SELECT ''合计'''
SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'--查询结果
exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去
+', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM'
+@tsql)
declare @tsql varchar(8000)declare @fdcount int
set @fdcount=50 --定义厉大慨多少个值可以在一个字符串变量中存储--生成动态列
--生成数据处理临时表
select id=identity(int,0,1),0 as groupid,0 as sortid,*
into #tb from(select DISTINCT TLMC FROM #t) a--对要处理的字段分组
update #tb set groupid=id/@fdcount,sortid=id % @fdcount--组合字符串
select @fdcount=@fdcount-1
,@sql1=''
,@sql2='' --如果有更多的变量,则继续写下去while @fdcount>=0
begin
select @sql1=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sql
FROM #tb where sortid=@fdcount and groupid=0 select @sql2=',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30))
+ ']'+@sq2
FROM #tb where sortid=@fdcount and groupid=1
--如果有更多的变量,继续写下去 set @fdcount=@fdcount-1
end--生成动态列
SET @tsql = ' UNION SELECT ''合计'''
SELECT @tsql = @tsql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(30)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(30)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @tsql = @tsql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t'--查询结果
exec('select 地区=dqdm'+@sql1+@sql2 --如果有更多的变量,继续写下去
+', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) AS 合计 FROM #t GROUP BY DQDM'
+@tsql)