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 ocelot@ocelot.com.pl