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:
