SQL存储过程实现SPSS交叉表（有图有真相）
/****** Object:StoredProcedure . Script Date: 03/27/2014 20:46:38 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc . (
@tableName nvarchar(255)
,@colName1 nvarchar(255)
,@colName2 nvarchar(255)
)
as
-- =============================================
-- Author: <Aric>
-- Create date: <03/27/2014>
-- 标题 ： 交叉表算法实现
-- 调用 ：
--DECLARE @return_value int
--EXEC@return_value = .
-- @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'',N'U') is not null begin drop table end
CREATE TABLE .(
(500) NULL,
(500) NOT NULL,
NULL
) ON
;
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 ' 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 ' as colName1 from Temp_CrossTable_001) t
group by
id
;
set @str = ('
if object_id(N'''',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()
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'''',N''U'') is not null begin drop table 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
调用SP:
页:
[1]