SQL存储过程实现SPSS交叉表（有图有真相）

SP代码：

/****** Object:  StoredProcedure [dbo].[Pro_CrossTable]    Script Date: 03/27/2014 20:46:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER 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 
		 ‘+@colName1+‘ 
		,‘+@colName2+‘
		,count(*)     
		from ‘+@tableName+‘
		where ‘+@colName1+‘ is not null
		group by ‘+@colName1+‘,‘+@colName2+‘
		‘)
		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 *,‘+@str3+‘ 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 *,‘+@str3+‘ 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 ‘+@colName2+‘ ,  
		‘+@str4+‘
		--into Out_CrossTable_Percent
		from Out_CrossTable_Value t1 ,(
		select ‘+@str1+‘
		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

SQL存储过程实现SPSS交叉表（有图有真相）,古老的榕树,5-wow.com