程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL存儲過程實現SPSS交叉表

SQL存儲過程實現SPSS交叉表

編輯:關於SqlServer

       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:

    SQL存儲過程實現SPSS交叉表    三聯
    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved