Implementation of Common Dynamic cross tabulation)

-- ===================================================== ============
-- Implementation of universal dynamic cross tabulation
-------------------------------------------------------------------------------

-- Usp_crosstab
-------------------------------------------------------------------------------

Create procedure usp_crosstab
@ Table varchar (50), -- table name
@ Sumrow varchar (8000), -- total column Fields
@ Grouprow varchar (1000), -- group column Field
@ Col varchar (50), -- convert the column value to the column Field
@ Coltable varchar (1000), -- the column field table to convert the column value to the column
@ Sortfield varchar (100), -- Sort field of the column field table to convert the column value to the column
@ Subsortfield varchar (100), -- convert the column value to the column field table sub-sorting field of the column
@ Content varchar (50), -- convert the column value to the content field of the column after the column
@ Showsum varchar (50), -- display the total column name
@ Showsubtotal varchar (50), -- display the subtotal Column
@ Where varchar (8000) -- Query Condition
As
Declare @ declaresql varchar (8000), -- SQL statement for variable definition
@ Initsql varchar (8000), -- SQL of the initial variable
@ Subsectionsql varchar (8000), -- read the SQL statement that reads data after 4000 segments
@ Combinationsql varchar (8000), -- the SQL statement that combines various variables after segmentation
@ Endfromsql varchar (8000), -- SQL statement of the combined suffix (Table Name, group column name)
@ I int, -- according to 4000, the I obtained by piecewise Iteration
@ IC varchar (50), -- convert it to the I after the string
@ Mc varchar (50) -- number of variables

Exec ('select id = identity (INT,), Col = ''sum (case '+ @ Col + 'when

''''' + '+ @ Col +' + ''''' then' + @ content + 'else 0 end) as ''' + '+ @ Col +' + '''''',''

, Gid = 0, sort = '+ @ sortfield +', subsort = '+ @ subsortfield + 'into # TMP from (select

'+ @ Col +' = Replace ('+ @ Col + ','''''''',''''''''''''), '+ @ sortfield +', '+ @ subsortfield +' from

'+ @ Coltable +') ')

Select @ I = max (LEN (COL) from # TMP
Set @ I = 4000/@ I
 
Update ## TMP set gid = ID/@ I
 
Select @ I = max (GID), @ MC = cast (@ I as varchar (50) from # TMP
Select

@ Declaresql = '', @ initsql ='', @ subsectionsql = '', @ combinationsql ='', @ endfromsql =''
 
 
While @ I> = 0
Begin
Select @ Ic = cast (@ I as varchar (50), @ I = @ I-1, @ declaresql = '@' + @ IC +'

Varchar (4000), '+ @ declaresql, @ initsql =' @ '+ @ IC +' = ''', '+ @ initsql,
@ Subsectionsql = 'select @ '+ @ IC +' = @ '+ @ IC +' + Col from # TMP where

Gid = ''' + @ IC + ''' order by sort, subsort '+ char (13)

+ @ Subsectionsql, @ combinationsql = '@' + @ IC + '+ @ combinationsql
End
 
Set @ endfromsql = ''' into # crosstable from '+ @ table + 'where' + @ where + 'group

'+ @ Grouprow + ''''
Select @ declaresql = 'desc' + Left (@ declaresql, Len (@ declaresql)-1) + char

(13), @ initsql = 'select' + Left (@ initsql, Len (@ initsql)-1) + char (13), @ combinationsql = left

(@ Combinationsql, Len (@ combinationsql)-1)
Exec (@ declaresql + @ initsql + @ subsectionsql +
'Set @ '+ @ MC +' = left (@ '+ @ MC +', Len (@ '+ @ MC +')-1) '+
'Exec (''select' + @ sumrow + ',' + @ combinationsql + '+ @ endfromsql + ')'
)

Exec ('Update # crosstable set' + @ showsum + '= ''total'',' + @ showsubtotal + '= NULL

Where '+ @ showsum +' is null ')

Select * from # crosstable
Drop table # TMP
Drop table # crosstable

Go

 

 

Http://hi.baidu.com/sinye/blog/item/75c4a75181732d8f8c543088.html