SP代码：
/****** Object: StoredProcedure [dbo].[Pro_CrossTable] Script Date: 03/27/2014 20:46:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[Pro_CrossTable] ( @tableName nvarchar(255) ,@colName1 nvarchar(255) ,@colName2 nvarchar(255))as-- =============================================-- Author: <Aric>-- Create date: <03/27/2014>-- 标题 ： 交叉表算法实现-- 调用 ：--DECLARE @return_value int--EXEC @return_value = [dbo].[Pro_CrossTable]-- @tableName = N'temp_A063', --表名-- @colName1 = N'ageArrange', --列名1（转置列）-- @colName2 = N'indate' --列名2--SELECT 'Return Value' = @return_value--GO-- =============================================begin begin try begin tran begin -- select * from Temp_CrossTable_001 if object_id(N'[Temp_CrossTable_001]',N'U') is not null begin drop table [Temp_CrossTable_001] end CREATE TABLE [dbo].[Temp_CrossTable_001]( [colName1] [nvarchar](500) NULL, [colName2] [nvarchar](500) NOT NULL, [Value] [float] NULL ) ON [PRIMARY] ; exec(' insert into Temp_CrossTable_001 select [email protected]+' ,[email protected]+' ,count(*) from [email protected]+' where [email protected]+' is not null group by [email protected]+',[email protected]+' ') end declare @str nvarchar(2000),@str1 nvarchar(500),@str2 nvarchar(500),@str3 nvarchar(500),@str4 nvarchar(500) select @str1=stuff((select ', '+colName1 from (select distinct 1 as ID , '['+colName1+']' as colName1 from Temp_CrossTable_001) t where id=t.id for xml path('')),1,1,'') from (select distinct 1 as ID , '['+colName1+']' as colName1 from Temp_CrossTable_001) t group by id ; select @str2=stuff((select ', '+colName1 from (select distinct 'sum_col' as ID , 'sum(['+colName1+'])' as colName1 from Temp_CrossTable_001) t where id=t.id for xml path('')),1,1,'') from (select distinct 'sum_col' as ID , 'sum(['+colName1+'])' as colName1 from Temp_CrossTable_001) t group by id ; select @str3=stuff((select '+ '+colName1 from (select distinct 1 as ID , '['+colName1+']' as colName1 from Temp_CrossTable_001) t where id=t.id for xml path('')),1,1,'') from (select distinct 1 as ID , '['+colName1+']' as colName1 from Temp_CrossTable_001) t group by id ; select @str4=stuff((select ', '+colName1 from (select distinct 1 as ID , 't1.['+colName1+'], t1.['+colName1+']/convert(float,t2.['+colName1+']) as [N%]' as colName1 from Temp_CrossTable_001) t where id=t.id for xml path('')),1,1,'') from (select distinct 1 as ID , 't1.['+colName1+'], t1.['+colName1+']/convert(float,t2.['+colName1+']) as [N%]' as colName1 from Temp_CrossTable_001) t group by id ; set @str = (' if object_id(N''[Out_CrossTable_Value]'',N''U'') is not null begin drop table Out_CrossTable_Value end SELECT *,[email protected]+' as sum_row into Out_CrossTable_Value FROM( select convert(nvarchar(255),colName1) as colName1,convert(nvarchar(255),colName2) as colName2,Value from Temp_CrossTable_001 m ) P PIVOT ( SUM(Value) FOR colName1 IN ('+ @str1 +') ) AS T union all select ''sum_col'',' + @str2 + ' , sum([sum_row]) from ( SELECT *,[email protected]+' as sum_row FROM( select convert(nvarchar(255),colName1) as colName1,convert(nvarchar(255),colName2) as colName2,Value from Temp_CrossTable_001 m ) P PIVOT ( SUM(Value) FOR colName1 IN ('+ @str1 +') ) AS T ) t ') exec (@str) set @str =' --if object_id(N''[Out_CrossTable_Percent]'',N''U'') is not null begin drop table [Out_CrossTable_Percent] end select t1.colName2 as [email protected]+' , [email protected]+' --into Out_CrossTable_Percent from Out_CrossTable_Value t1 ,( select [email protected]+' from Out_CrossTable_Value where colName2=''sum_col'') t2 ' exec (@str) --------------------------------------结果： --select * from Out_CrossTable_Percent commit tran return 0 end try begin catch rollback tran return 1 end catch end