SQL stored procedure Implementation of SPSS cross tabulation

Source: Internet
Author: User

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 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 48 49 50 51 52 54 55 57 58 59 60 61 62 63 64 65 66 67 68 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 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> -- Title: cross tabulation 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' -- column name 2 -- SELECT 'Return value' = @ return_value -- GO -- ============ ============================== begin try in try tran in -- 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 (''),'') 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 (''),'') 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 partition (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 terminate (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) ------------------------------------------ result: -- select * from Out_CrossTable_Percent commit tran return 0 end try begin catch rollback tran return 1 end catch end

Call SP:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.