Ocelot Systemy Informatyczne

  • polski

CrossTab! is a fast way to execute cross queries (PIVOT type) on MS SQL Server 2000, 2005, 2008 and receive the result in a form of a clear table. With just one universal procedure with few parameters you can easily create queries which work as the Pivot table well known from MsExcel or as the CrossTab from MsAccess. You will obtain cross tab results in very short time.

The exemplary invoke:
EXEC dbo.CrossTab 
    -- data select
    'SELECT * FROM oce_mbo.AuditUser WHERE AuditUserID>0 ORDER BY UserName'
    -- grouping column
    ,'UserName'
    -- agregate column (optional)
    ,'Avg(AuditUserID) AS ID'
    -- order by (optional)
    ,NULL
    -- PIVOT column name source
    ,'UserName'
    -- PIVOT value
    ,'Min([AuditUserID])+Max([AssociateID])'
    -- PIVOT fixed columns (optional)
    --,'[Ablink], [FZoom], [Maxi]'

The exemplary result:

UserName ID Ablink Dword EMat FZoom HDry Maxi Supero
Ablink 1 2 NULL NULL NULL NULL NULL NULL
Dword 166 NULL 332 NULL NULL NULL NULL NULL
EMat 134 NULL NULL 268 NULL NULL NULL NULL
FZoom 162 NULL NULL NULL 324 NULL NULL NULL
HDry 2 NULL NULL NULL NULL 4 NULL NULL
Maxi 121 NULL NULL NULL NULL NULL 242 NULL
Supero 3 NULL NULL NULL NULL NULL NULL 6

CrossTab! is an universal procedure which lets you create and execute any cross query with the source of data in another query or procedure. As a result you will get a clear table with the data. The number of generated columns in this table can be limited to chosen ones, which are a subset of the result.

The CrossTab! procedure enables to execute a test query which returns all names of the columns which will be generated in a case of the full procedure execution.

The exemplary invoke:
EXEC dbo.CrossTab 
    'SELECT * FROM oce_mbo.AuditUser WHERE AuditUserID>0 ORDER BY UserName'
    ,'UserName'
    ,'Avg(AuditUserID) AS ID'
    ,NULL
    ,'UserName'
    ,'Min([AuditUserID])+Max([AssociateID])'
    ,NULL
    ,NULL
    -- return column names only (optional)
    ,1

The exemplary result:

ColumnName
Ablink
Dword
EMat
FZoom
HDry
Supero

The additional functionality lets sort out the returned data, avoid records with a NULL value in a column name, turn on the debagging mode to control the procedure and to find errors in base queries.

The CrossTab! procedure will fulfill users' expectations in term of the flexibility of invoke parameters and high speed of cross query execution.

The CrossTab! doesn't require any additional installation on the server. It is initiated as a procedure in the destination database. It doesn't require Reporting Services to work. The entire solution is just a one procedure!

Data Base: Microsoft SQL Server: 2000, 2005, 2008
License: three types of license are available:
database - to use in the selected database,
server - to use on one server instance without databases limit,
developer - for developing with your own applications
Data Base: Microsoft SQL Server: 2000, 2005, 2008
Related products: GoScript! for MsSQL, DataAudit! for MsSQL

Do you need any additional information?
Please, call:   +48 42 230 38 55.
or write us:   e-mail

update date: 2017-11-19 07:16
visits: 322,345
© 2017 OCELOT Systemy Informatyczne. All rights reserved.