SQL stored procedure realizes SPSS cross table

SP Code:
?

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 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 5, 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 11 9 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>--Title: Cross-table algorithm implementation--call:--declare   @return_value int--exec  @return_value = [dbo]. [Pro_crosstable]--      @tableName = N ' temp_a063 ',  --table name--       @colName1 = n ' agearrange ',  --column name 1 (transpose column)--      @colName2 = n ' indate ' & nbsp;      --Column Name 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    & Nbsp;    create TABLE [dbo]. [temp_crosstable_001] (            [colname1] [nvarchar] (m) NULL,              [COLNAME2] [nvarchar] (+) not NULL,              [value] [float] NULL          ) on [PRIMARY]         ;         exec (' &nbsP;       insert into temp_crosstable_001          select           ' + @colName1 + '         &nbsp, ' + @colName2 + '         ,count (*)               from ' + @tableName + '          where ' + @colName1 + ' is not null         group by ' +@ colname1+ ', ' + @colName2 + '          ')          end                              declare @str nvarchar, @str1 nvarchar (500) , @str2 nvarchar, @str3 nvarchar, @str4 nvarchar           select              @str1 =stuff (select ', ' +colname1 From (select DISTINCT 1 as ID, ' [' +colname1+ '] ' as colName1 to 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+ ']) ' A S 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 fro M 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     &NB Sp;    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, ' ')     & Nbsp;     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       & Nbsp; 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  & Nbsp;      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)                   --------------------------------------Results:         --select * FROM Out_crosstable_percent             commit Tran   & Nbsp;     return 0       end try     begin Catch         rollback Tran          return 1     end Catch end

Call SP: