Ocelot Systemy Informatyczne

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:

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:

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!

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