IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_qry]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_qry] Go
/ * - Generate the simple universal stored procedure of the cross-table according to the specified table name, the cross-section, the statistical field, and automatically generate cross-length tables and can generate a total of two directions according to the needs of the need.
Note that if the number of transverse fields is greater than the number of longitudinal fields, if you do not have this function, remove the switching section.
Zou Jian 204.06 (please keep this information) - * /
/ * - Call example
EXEC P_QRY 'SYSCOLUMNS', 'ID', 'Colid', 'Colid', 1, 1 - * /
Create Proc P_Qry @ TableName Sysname, - Table Name @ 轴 sysname, - The leftmost column @ horizontal axis sysname, - Cross table top @ Body content sysname, - Data Field @ 是 加 向 计 b - 1 表 表 表 家 计 计 计 计 计 计 计 家 计 向 计 计 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 向 加 加 向 加 加 加 加 加 加 加 加 加 加 加 加 加 @ 加 @ @SQL VARCHAR (8000)
- Determine if the horizontal field is greater than the number of longitudinal fields, if yes, exchange translators set @ s = 'declare @a sysnameif (SELECT CASE WHEN COUNT (Distinct [' @ vertical axis ') from [' @ Tablename ']) = 1 Select @ a = @ vertical axis, @ 轴 = @ horizontal axis, @ 轴 = @ a'exec sp_executesql @S, n' @ vertical axis sysname out, @ horizontal axis sysname out ', @ 纵Axis OUT, @ 轴 OUT
- Generate cross table treatment statement set @ s = 'set @S =' '' 'select @ s = @ S ' ', [' ' CAST ([' @ horizontal '] as varchar) ' ' ] = SUM (Case [' @ 轴 '] when '' '' '' Cast ([' @ Horizontal '] As Varchar) '' '' 'Then [' @ Body Content '] Else 0 end)' 'from [' @ Tablename '] Group by [' @ horizontal axis ']' exec sp_executesql @s, n '@ s varchar (8000) OUT', @ SQL OUT
- Whether to generate a total field of processing declare @ Sum1 varchar (200), @ Sum2 varchar (200), @ Sum3 varchar (200) SELECT @ Sum1 = Case @ 是 加 加 合 合 合 1 Then ', [Total] = SUM [' @ Body content ']) 'else' 'end, @ Sum2 = Case @ 是 家 合 合 合 1 TEN' [@ 轴 '] = Case Grouping ([' @ 轴 ']) WHEN 1 THEN' '' '' Else Cast ([' @ vertical axis '] as varchar) end 'else' [' @ vertical axis '] End, @ Sum3 = case @ 是家Total WHEN 1 THEN 'with ROLLUP' ELSE 'END - Generate Interchange EXEC (' SELECT ' @ Sum2 @ SQL @ Sum1 ' from [' @ Tablename '] Group By [' @ 轴 '] ' @ Sum3) Go
