SQL預存程序實現SPSS交叉表

 　　SP代碼：
?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140

/****** 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

　　調用SP: