DECLARE @sql VARCHAR(8000)
DECLARE @tsql VARCHAR(8000)
SET @sql = 'select dqdm as ''地区'''
--生成动态列
SELECT @sql = @sql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t GROUP BY DQDM' SET @tsql = ' UNION SELECT ''合计'''
--生成动态列
SELECT @tsql = @tsql + ',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
EXEC(@sql + @tsql+ ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t')
DECLARE @tsql VARCHAR(8000)
SET @sql = 'select dqdm as ''地区'''
--生成动态列
SELECT @sql = @sql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t GROUP BY DQDM' SET @tsql = ' UNION SELECT ''合计'''
--生成动态列
SELECT @tsql = @tsql + ',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
EXEC(@sql + @tsql+ ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t')
SELECT @sql = @sql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t GROUP BY DQDM'这时的 @sql 已经超过 3999 个字符了(其他被截断了), 我应该怎么办?
SELECT @sql = @sql + ', SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
SET @sql = @sql + ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t GROUP BY DQDM'@sql 已经超出 3999 个字符(我不知道为什么不是 7999 )..怎么办??(事实也是如此:(...
DECLARE @tsql VARCHAR(8000)
set @sql=''
--生成动态列
SELECT @sql = @sql + ',SUM(CASE tlmc WHEN '''+CAST(tlmc AS VARCHAR(50))+''' THEN 1 ELSE 0 END) ['+CAST(tlmc AS VARCHAR(50))+']'
FROM (SELECT DISTINCT TLMC FROM #t) a SET @tsql =''
--生成动态列
SELECT @tsql = @tsql + ',SUM(CASE tlmc WHEN ''' + CAST(tlmc AS NVARCHAR(50)) + ''' THEN 1 ELSE 0 END) AS [' + CAST(tlmc AS NVARCHAR(50)) + ']' FROM (SELECT DISTINCT TLMC FROM #t) AS a
EXEC('select dqdm [地区]'+@sql+', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t GROUP BY DQDM UNION SELECT ''合计'''+ @tsql+ ', SUM(CASE WHEN tlmc IS NOT NULL THEN 1 ELSE 0 END) 合计 FROM #t')
(所影响的行数为 352 行),SUM(CASE tlmc WHEN '奥林匹克广场' THEN 1 ELSE 0 END) [奥林匹克广场],SUM(CASE tlmc WHEN '八佰伴' THEN 1 ELSE 0 END) [八佰伴]..............,SUM(CASE tlmc WHEN '百佳超市' THEN 1 ELSE 0 END) [百佳超市]
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: ',' 附近有语法错误。现在大概有 80 个通路, @sql 还是超出限制...将来可能还会有90, 100 个..:(能不能用 ntext 来代替?(我有试过, 但不能在存储过程内部定义, 说是局部变量不能用 ntext, image等类型)
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)