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: -- 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:
