--建立测试环境 Create Table Drug(DrugName Nvarchar(10),DrugCode Nvarchar(10)) Insert Drug Select N'西药费','a' Union All Select N'中药费','b' Union All Select N'草药费','c'Create table DrugFare(PersonName Nvarchar(10),DrugFare Numeric(10,1),DrugCode Nvarchar(10)) Insert DrugFare Select N'张三',50.0, 'a' Union All Select N'张三',100.0,'b' Union All Select N'李四',60.0 ,'a' GO--处理 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(',SUM(Case When B.PersonName=N'''+PersonName +''' Then DrugFare Else 0 End ) As '+ PersonName as nvarchar(4000)) INTO # FROM( Select Distinct PersonName from DrugFare)a--分组临时表 UPDATE a SET @i=id/i,g=@i FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b SET @ic=@i--生成数据处理语句 SELECT @sqlhead=N'''' +REPLACE(N'Select A.DrugName',N'''',N'''''') +'''', @sqlend=N'''' +REPLACE(N' from Drug A Left Join DrugFare B On A.DrugCode=B.DrugCode Group By A.DrugName',N'''',N'''''') +N'''', @sql1=N'',@sql2=N'',@sql3=N'',@sql4=N'' WHILE @ic>=0 SELECT @sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1, @sql2=N',@'+@ic+N'=N'''''+@sql2, @sql3=N',@'+@ic +N'=CASE g WHEN '+@ic +N' THEN @'+@ic+N'+a ELSE @'+@ic +N' END'+@sql3, @sql4=N'+@'+@ic+@sql4, @ic=@ic-1 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 '+@sql1+N' SELECT '+@sql2+N' SELECT '+@sql3+N' FROM # EXEC(N'+@sqlhead+N'+'+@sql4+N'+N'+@sqlend+N')') --删除临时表 DROP TABLE # GO--删除测试环境 Drop Table Drug,DrugFare
超过8000
Create Table Drug(DrugName Nvarchar(10),DrugCode Nvarchar(10))
Insert Drug Select N'西药费','a'
Union All Select N'中药费','b'
Union All Select N'草药费','c'Create table DrugFare(PersonName Nvarchar(10),DrugFare Numeric(10,1),DrugCode Nvarchar(10))
Insert DrugFare Select N'张三',50.0, 'a'
Union All Select N'张三',100.0,'b'
Union All Select N'李四',60.0 ,'a'
GO--处理
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(',SUM(Case When B.PersonName=N'''+PersonName +''' Then DrugFare Else 0 End ) As '+ PersonName
as nvarchar(4000))
INTO # FROM(
Select Distinct PersonName from DrugFare)a--分组临时表
UPDATE a SET @i=id/i,g=@i
FROM # a,(SELECT i=3800/MAX(LEN(a)) FROM #)b
SET @ic=@i--生成数据处理语句
SELECT
@sqlhead=N''''
+REPLACE(N'Select A.DrugName',N'''',N'''''')
+'''',
@sqlend=N''''
+REPLACE(N' from Drug A Left Join DrugFare B On A.DrugCode=B.DrugCode Group By A.DrugName',N'''',N'''''')
+N'''',
@sql1=N'',@sql2=N'',@sql3=N'',@sql4=N''
WHILE @ic>=0
SELECT
@sql1=N',@'+@ic+N' nvarchar(4000)'+@sql1,
@sql2=N',@'+@ic+N'=N'''''+@sql2,
@sql3=N',@'+@ic
+N'=CASE g WHEN '+@ic
+N' THEN @'+@ic+N'+a ELSE @'+@ic
+N' END'+@sql3,
@sql4=N'+@'+@ic+@sql4,
@ic=@ic-1
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 '+@sql1+N'
SELECT '+@sql2+N'
SELECT '+@sql3+N' FROM #
EXEC(N'+@sqlhead+N'+'+@sql4+N'+N'+@sqlend+N')')
--删除临时表
DROP TABLE #
GO--删除测试环境
Drop Table Drug,DrugFare