1 CREATE PROCEDURE [dbo].[SP_DynamicCrosstab]
2 /* SP_DynamicCrosstab
3 * 描述：生成交叉报表
4 */
5 --@tableSpec
6 --A、表或视图
7 --B、存储过程句称
8 @tableSpec NVARCHAR(4000) = '',
9
10 --@sqlSelect
11 --A、表SELECT语句:'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'
12 --B、存储过程:'EXEC dbo.myStoredProcedure <param1>, <param2>'
13 @sqlSelect NVARCHAR(512),
14
15 --@sqlFromWhere
16 --如果数据源是存储过程时可为空
17 @sqlFromWhere NVARCHAR(512) = '',
18
19 --@pivotExpr
20 --表头分组字段
21 @pivotExpr NVARCHAR(255),
22
23 --@valueExpr
24 --交叉表计算的字段
25 @valueExpr NVARCHAR(512),
26
27 --@function
28 --统计函数 如:SUM,MIN等
29 @function NVARCHAR(20) = '',
30
31 --@groupBy
32 --表行分组字段
33 @groupBy NVARCHAR(512),
34
35 --@sortLookup
36 --排序字段
37 @sortLookup NVARCHAR(40) = '',
38
39 --@collation
40 --SQL语句执行环境标识
41 @collation NVARCHAR(40) = 'Chinese_PRC_CI_AS',
42
43 --@verbose
44 --消息标识 0:不显示消息,1:显示消息
45 @verbose BIT = 0
46
47 AS
48 BEGIN
49
50 -- 检测命令参数不为空
51 IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0
52 BEGIN
53 RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)
54 ROLLBACK TRANSACTION
55 END
56
57 IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0
58 BEGIN
59 RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)
60 ROLLBACK TRANSACTION
61 END
62
63 IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0
64 BEGIN
65 RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)
66 ROLLBACK TRANSACTION
67 END
68
69 IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0
70 BEGIN
71 RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)
72 ROLLBACK TRANSACTION
73 END
74
75 --本地变量
76 DECLARE @i INTEGER
77 DECLARE @sql NVARCHAR(4000)
78 DECLARE @sqlX NVARCHAR(4000)
79 DECLARE @col NVARCHAR(4000)
80 DECLARE @pivot NVARCHAR(4000)
81 DECLARE @indx NVARCHAR(10)
82 DECLARE @cols NVARCHAR(4000)
83 DECLARE @where NVARCHAR(4000)
84 DECLARE @update NVARCHAR(4000)
85 DECLARE @value NVARCHAR(532)
86 DECLARE @select NVARCHAR(4000)
87
88 --删除临时表(如果存在)
89 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
90 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
91 EXEC sp_executesql @sqlX
92 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
93 EXEC sp_executesql @sqlX
94 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
95 EXEC sp_executesql @sqlX
96 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
97 EXEC sp_executesql @sqlX
98 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
99 EXEC sp_executesql @sqlX
100 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
101 EXEC sp_executesql @sqlX
102
103 IF @verbose = 1 SELECT '删除全局临时表(如果存在)' AS [消息]
104
105 -- 存储输入数据集
106 IF LEN(RTRIM(@tableSpec)) = 0
107 BEGIN
108 SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere
109 IF @verbose = 1 SELECT @sql AS [创建临时表,并填充数据]
110 EXEC sp_executesql @sql
111 END
112 ELSE
113 BEGIN
114 --如果输入数据来自存储过程时创建临时表
115 SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'
116 IF @verbose = 1 SELECT @sql AS [创建临时表]
117 EXEC sp_executesql @sql
118 -- Populate it
119 SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere
120 IF @verbose = 1 SELECT @sql AS [填充临时表数据]
121 EXEC sp_executesql @sql
122 END
123 IF @verbose = 1 SELECT * FROM ##input
124
125 -- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!
126 IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')
127 BEGIN
128 RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)
129 ROLLBACK TRANSACTION
130 END
131
132 -- Add an identity column (we need a 'row ID')
133 ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL
134
135 -- Generate the column list
136 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'
137 IF @verbose = 1 SELECT @sql AS [生成列清单]
138 EXEC sp_executesql @sql
139 IF @verbose = 1 SELECT * FROM ##colList
140
141 -- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them
142 IF RTRIM(ISNULL(@function, '')) = ''
143 BEGIN
144 SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
145 END
146 ELSE
147 BEGIN
148 SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
149 END
150 IF @verbose = 1 SELECT @sql AS [创建##grpCount临时表]
151 EXEC sp_executesql @sql
152 IF @verbose = 1 SELECT * FROM ##grpCount
153
154 -- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)
155 SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0
156
157 -- Build the results table; one row per group
158 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy
159 IF @verbose = 1 SELECT @sql AS [创建##results临时表]
160 EXEC sp_executesql @sql
161 IF @verbose = 1 SELECT * FROM ##results
162
163 -- Build the column list, taking into account duplicate occurences of pivotal values
164 DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]
165 OPEN xcursor
166
167 FETCH NEXT FROM xcursor INTO @pivot, @indx
168
169 WHILE @@FETCH_STATUS = 0
170 BEGIN
171
172 SELECT @i = 1
173
174 -- Loop over indx
175 WHILE @i <= @indx
176 BEGIN
177
178 -- Build the column list
179 SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END
180 INSERT INTO ##lookup VALUES (@pivot, @i, @col)
181 SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'
182 SELECT @cols = ISNULL(@cols + ', ', '') + @col
183
184 -- Add the column to the results table
185 SELECT @sql = 'ALTER TABLE ##results ADD ' + @col
186 IF @verbose = 1 SELECT @sql AS [增加临时表##results列]
187 EXEC sp_executesql @sql
188
189 -- Continue
190 SELECT @i = @i + 1
191
192 END
193
194 FETCH NEXT FROM xcursor INTO @pivot, @indx
195
196 END
197
198 CLOSE xcursor
199 DEALLOCATE xcursor
200
201 IF @verbose = 1 SELECT * FROM ##lookup
202 IF @verbose = 1 SELECT * FROM ##results
203
204 -- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause
205 DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]
206 OPEN xcursor
207
208 FETCH NEXT FROM xcursor INTO @indx, @col
209
210 WHILE @@FETCH_STATUS = 0
211 BEGIN
212
213 -- Build the WHERE clause
214 SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'
215
216 FETCH NEXT FROM xcursor INTO @indx, @col
217
218 END
219
220 CLOSE xcursor
221 DEALLOCATE xcursor
222
223 IF @verbose = 1 SELECT @where AS [WHERE语句]
224
225 -- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards
226 IF RTRIM(ISNULL(@sortLookup, '')) = ''
227 BEGIN
228
229 -- If no sorting table was specified, just select all columns
230 SELECT @select = '*'
231
232 -- Sort alphabetically
233 SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'
234
235 END
236 ELSE
237 BEGIN
238
239 -- The 'group by' column(s) always come(s) first
240 SELECT @select = @groupBy
241
242 -- Sort in proscribed order
243 SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'
244
245 END
246
247 IF @verbose = 1 SELECT @sql AS [构建##temp临时表]
248 EXEC sp_executesql @sql
249 IF @verbose = 1 SELECT * FROM ##temp
250
251 -- The value expression
252 SELECT @value = @valueExpr
253
254 -- If a function has been specified
255 IF RTRIM(ISNULL(@function, '')) != ''
256 BEGIN
257 SELECT @value = @function + '(' + @value + ')'
258 END
259
260 DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp
261 OPEN xcursor
262
263 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
264
265 WHILE @@FETCH_STATUS = 0
266 BEGIN
267
268 -- Build the SELECT expression
269 IF @select != '*' SELECT @select = @select + ', ' + @col
270
271 -- Create the SET clause of the UPDATE sql
272 IF RTRIM(ISNULL(@function, '')) = ''
273 BEGIN
274 -- No function specified
275 SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'
276 END
277 ELSE
278 BEGIN
279 -- Function specified
280 SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'
281 END
282 SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'
283 IF @verbose = 1 SELECT @sql AS [创建UPDATE的SET语句]
284 EXEC sp_executesql @sql
285
286 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
287
288 END
289
290 CLOSE xcursor
291 DEALLOCATE xcursor
292
293 -- Return the results
294 SELECT @sql = 'SELECT ' + @select + ' FROM ##results'
295 IF @verbose = 1 SELECT @sql AS [创建SELECT语句返回结果集]
296 EXEC sp_executesql @sql
297
298 -- Tidy up: drop the global temporary tables
299 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
300 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
301 EXEC sp_executesql @sqlX
302 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
303 EXEC sp_executesql @sqlX
304 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
305 EXEC sp_executesql @sqlX
306 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
307 EXEC sp_executesql @sqlX
308 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
309 EXEC sp_executesql @sqlX
310 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
311 EXEC sp_executesql @sqlX
312
313 END
314
2 /* SP_DynamicCrosstab
3 * 描述：生成交叉报表
4 */
5 --@tableSpec
6 --A、表或视图
7 --B、存储过程句称
8 @tableSpec NVARCHAR(4000) = '',
9
10 --@sqlSelect
11 --A、表SELECT语句:'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'
12 --B、存储过程:'EXEC dbo.myStoredProcedure <param1>, <param2>'
13 @sqlSelect NVARCHAR(512),
14
15 --@sqlFromWhere
16 --如果数据源是存储过程时可为空
17 @sqlFromWhere NVARCHAR(512) = '',
18
19 --@pivotExpr
20 --表头分组字段
21 @pivotExpr NVARCHAR(255),
22
23 --@valueExpr
24 --交叉表计算的字段
25 @valueExpr NVARCHAR(512),
26
27 --@function
28 --统计函数 如:SUM,MIN等
29 @function NVARCHAR(20) = '',
30
31 --@groupBy
32 --表行分组字段
33 @groupBy NVARCHAR(512),
34
35 --@sortLookup
36 --排序字段
37 @sortLookup NVARCHAR(40) = '',
38
39 --@collation
40 --SQL语句执行环境标识
41 @collation NVARCHAR(40) = 'Chinese_PRC_CI_AS',
42
43 --@verbose
44 --消息标识 0:不显示消息,1:显示消息
45 @verbose BIT = 0
46
47 AS
48 BEGIN
49
50 -- 检测命令参数不为空
51 IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0
52 BEGIN
53 RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)
54 ROLLBACK TRANSACTION
55 END
56
57 IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0
58 BEGIN
59 RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)
60 ROLLBACK TRANSACTION
61 END
62
63 IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0
64 BEGIN
65 RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)
66 ROLLBACK TRANSACTION
67 END
68
69 IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0
70 BEGIN
71 RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)
72 ROLLBACK TRANSACTION
73 END
74
75 --本地变量
76 DECLARE @i INTEGER
77 DECLARE @sql NVARCHAR(4000)
78 DECLARE @sqlX NVARCHAR(4000)
79 DECLARE @col NVARCHAR(4000)
80 DECLARE @pivot NVARCHAR(4000)
81 DECLARE @indx NVARCHAR(10)
82 DECLARE @cols NVARCHAR(4000)
83 DECLARE @where NVARCHAR(4000)
84 DECLARE @update NVARCHAR(4000)
85 DECLARE @value NVARCHAR(532)
86 DECLARE @select NVARCHAR(4000)
87
88 --删除临时表(如果存在)
89 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
90 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
91 EXEC sp_executesql @sqlX
92 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
93 EXEC sp_executesql @sqlX
94 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
95 EXEC sp_executesql @sqlX
96 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
97 EXEC sp_executesql @sqlX
98 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
99 EXEC sp_executesql @sqlX
100 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
101 EXEC sp_executesql @sqlX
102
103 IF @verbose = 1 SELECT '删除全局临时表(如果存在)' AS [消息]
104
105 -- 存储输入数据集
106 IF LEN(RTRIM(@tableSpec)) = 0
107 BEGIN
108 SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere
109 IF @verbose = 1 SELECT @sql AS [创建临时表,并填充数据]
110 EXEC sp_executesql @sql
111 END
112 ELSE
113 BEGIN
114 --如果输入数据来自存储过程时创建临时表
115 SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'
116 IF @verbose = 1 SELECT @sql AS [创建临时表]
117 EXEC sp_executesql @sql
118 -- Populate it
119 SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere
120 IF @verbose = 1 SELECT @sql AS [填充临时表数据]
121 EXEC sp_executesql @sql
122 END
123 IF @verbose = 1 SELECT * FROM ##input
124
125 -- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!
126 IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')
127 BEGIN
128 RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)
129 ROLLBACK TRANSACTION
130 END
131
132 -- Add an identity column (we need a 'row ID')
133 ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL
134
135 -- Generate the column list
136 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'
137 IF @verbose = 1 SELECT @sql AS [生成列清单]
138 EXEC sp_executesql @sql
139 IF @verbose = 1 SELECT * FROM ##colList
140
141 -- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them
142 IF RTRIM(ISNULL(@function, '')) = ''
143 BEGIN
144 SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
145 END
146 ELSE
147 BEGIN
148 SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
149 END
150 IF @verbose = 1 SELECT @sql AS [创建##grpCount临时表]
151 EXEC sp_executesql @sql
152 IF @verbose = 1 SELECT * FROM ##grpCount
153
154 -- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)
155 SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0
156
157 -- Build the results table; one row per group
158 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy
159 IF @verbose = 1 SELECT @sql AS [创建##results临时表]
160 EXEC sp_executesql @sql
161 IF @verbose = 1 SELECT * FROM ##results
162
163 -- Build the column list, taking into account duplicate occurences of pivotal values
164 DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]
165 OPEN xcursor
166
167 FETCH NEXT FROM xcursor INTO @pivot, @indx
168
169 WHILE @@FETCH_STATUS = 0
170 BEGIN
171
172 SELECT @i = 1
173
174 -- Loop over indx
175 WHILE @i <= @indx
176 BEGIN
177
178 -- Build the column list
179 SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END
180 INSERT INTO ##lookup VALUES (@pivot, @i, @col)
181 SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'
182 SELECT @cols = ISNULL(@cols + ', ', '') + @col
183
184 -- Add the column to the results table
185 SELECT @sql = 'ALTER TABLE ##results ADD ' + @col
186 IF @verbose = 1 SELECT @sql AS [增加临时表##results列]
187 EXEC sp_executesql @sql
188
189 -- Continue
190 SELECT @i = @i + 1
191
192 END
193
194 FETCH NEXT FROM xcursor INTO @pivot, @indx
195
196 END
197
198 CLOSE xcursor
199 DEALLOCATE xcursor
200
201 IF @verbose = 1 SELECT * FROM ##lookup
202 IF @verbose = 1 SELECT * FROM ##results
203
204 -- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause
205 DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]
206 OPEN xcursor
207
208 FETCH NEXT FROM xcursor INTO @indx, @col
209
210 WHILE @@FETCH_STATUS = 0
211 BEGIN
212
213 -- Build the WHERE clause
214 SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'
215
216 FETCH NEXT FROM xcursor INTO @indx, @col
217
218 END
219
220 CLOSE xcursor
221 DEALLOCATE xcursor
222
223 IF @verbose = 1 SELECT @where AS [WHERE语句]
224
225 -- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards
226 IF RTRIM(ISNULL(@sortLookup, '')) = ''
227 BEGIN
228
229 -- If no sorting table was specified, just select all columns
230 SELECT @select = '*'
231
232 -- Sort alphabetically
233 SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'
234
235 END
236 ELSE
237 BEGIN
238
239 -- The 'group by' column(s) always come(s) first
240 SELECT @select = @groupBy
241
242 -- Sort in proscribed order
243 SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'
244
245 END
246
247 IF @verbose = 1 SELECT @sql AS [构建##temp临时表]
248 EXEC sp_executesql @sql
249 IF @verbose = 1 SELECT * FROM ##temp
250
251 -- The value expression
252 SELECT @value = @valueExpr
253
254 -- If a function has been specified
255 IF RTRIM(ISNULL(@function, '')) != ''
256 BEGIN
257 SELECT @value = @function + '(' + @value + ')'
258 END
259
260 DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp
261 OPEN xcursor
262
263 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
264
265 WHILE @@FETCH_STATUS = 0
266 BEGIN
267
268 -- Build the SELECT expression
269 IF @select != '*' SELECT @select = @select + ', ' + @col
270
271 -- Create the SET clause of the UPDATE sql
272 IF RTRIM(ISNULL(@function, '')) = ''
273 BEGIN
274 -- No function specified
275 SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'
276 END
277 ELSE
278 BEGIN
279 -- Function specified
280 SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'
281 END
282 SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'
283 IF @verbose = 1 SELECT @sql AS [创建UPDATE的SET语句]
284 EXEC sp_executesql @sql
285
286 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot
287
288 END
289
290 CLOSE xcursor
291 DEALLOCATE xcursor
292
293 -- Return the results
294 SELECT @sql = 'SELECT ' + @select + ' FROM ##results'
295 IF @verbose = 1 SELECT @sql AS [创建SELECT语句返回结果集]
296 EXEC sp_executesql @sql
297
298 -- Tidy up: drop the global temporary tables
299 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
300 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
301 EXEC sp_executesql @sqlX
302 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
303 EXEC sp_executesql @sqlX
304 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
305 EXEC sp_executesql @sqlX
306 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
307 EXEC sp_executesql @sqlX
308 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
309 EXEC sp_executesql @sqlX
310 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
311 EXEC sp_executesql @sqlX
312
313 END
314
浙公网安备 33010602011771号