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