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&
