http://blog.9cbs.net/demongz/archive/2005/03/22/327169.aspx
In the relational database, it is often considered to make the database more in line with the third paradigm while it is also considered from data access speed and spatial usage. When the data structure is designed, the recording length is minimized. Moreover, in order to make the procedure to make the procedure to make the data diversification requirements by user personalization. However, it is reflected to the end user as an intuitive report form, so it is necessary to use the technology of crosstab, and the author has a code, it is very useful, I hope to bring convenience to you.
CREATE PROCEDURE CrossTable - Cross table builder - (Select Distinct SuppCode As SCode, MCode, PurQtyRate As Rate-- From MtSPM A Inner Join MtSPD B On A.ID = B.MtSuppPriceM_ID-- Inner Join Suppliers C On A.Supp_ID = C.ID-- where a.active_kid = 120 and advas = 1) D @ vsourceTab as varchar (2000), - Data Source Table, can be table, view, or SQL statement (to be used in parentheses, alias: Segment) @vgroupbyfield as varchar (50), - by the SELCT Group BY, you can calculate multiple fields (record can have null values) @vtransformcol as varchar (50), - Total combined function calculation Value field @vfunction as varchar (50) = 'sum', - default value, function in the cross table, can also be the calculation formula of '2 * Sum' @vpivotcol as varchar (50), - To convert The field of the column, the only column, can be an expression 'field1 field2' (record can have a null value) @vstrwhere as varchar (500) = null --where constraint conditions, you can empty AS - important tips: @strsql Largest Size ALOWED IS 8000, so try to convert less field content to column Declare @strsql as varchar (8000) - // Total SQL statement declare @strsum as varchar (3000) - // PRAING DECLARE @pcols as VARCHAR (100) Declare @strwhere as varchar (500) Execute ('Declare Cursorcross Cursor for Select Distinct' @vpivotcol 'from' @ vSourceTab 'ORDER BY' @vpivotcol ' For read only ') Begin Set NoCount ON Set @strsql =' 'set @ strsum =' 'set @ pcols =' 'IF =' 'IF ='))) <> 'begin set @strwhere = 'WHERE' @vstrwhere '' endelse set @ strwhere = '' Open Cursorcross While (0 = 0) Begin Fetch next from cursorcross Into @pcols if (@@ fetch_status <> 0) Break if @pcols is null - // Not a null value, set @ pcols = 'null' - to prevent the title name of the newly created column, with @
VgroupByfield's field rename, - The title name of the newly created column increases the prefix [@vgroupBYFIELD. The title name of the newly created column] - Due to the SQL length limit max = 8000, the source data control field value cannot be NULL Therefore, this is no longer verified whether the value is null set @ strsql = @ strsql ',' @vfunction '(Case' @ vpivotcol 'when' @ Pcols 'Ten' @ VTRANSFORMCOL 'ELSE NULL END) AS ' ' @ Vpivotcol, 1) @ Pcols ']' - Due to length limit, no computes and set @ strsum = @ strsum ' ISNULL (a.' '[' Left (@ vpivotcol, 1) @ pcols ']' ', 0)' end set @STRSQL = 'SELECT' @vgroupbyfield ' @ strsql ' from ' @strwhere ' Group By ' @vgroupbyfield - Column is a total of field name' totalsum 'set @strsql =' select a. *, (0 ' @ strsum ') as totalsum from (' @strsql ') AS A ' Set @strsql = 'SELECT A. * from (' @ strsql ') AS A' Execute (@strsql) Close Cursorcross Deallocate Cursorcrossend - Crosstable 'Thcld', 'StoreID, DH', 'Qty', 'Sum', 'Goodscode', ''
