Cross Table – Lines from Table1 are columns and Lines from Table2 are lines

When you need to create a table that imitates an cross tab in mssql – you can create a temporary table that can get the columns from an table, and the rows form another table. At the cross for this two tables, can be a bit type who can show if the relation between those two table has some value.

First you need to see the  table schema.

schema

All the rows from table CodeTypes will be considered like table columns.

All the rows from table CodeContents will be considered like table lines.

The table CodeContentPerCodeType contains the cross values between this two tables.

The mechanism for this stored procedure is the following:

1. Create dinamic the “temporary table” with columns taken from the first table.

2. Foreach line from second table check is exists some value in “CodeContentPerCodeType”. If exists then will make an insert into “temporary table”.

3. Show the cross values inserted into “temporary table”.

The following stored procedure uses cursors for iterating through table lines.

The final result for the execution of this SP is the following.

schema

In my jobs application this combination of table and rows are shown like an table with check boxes.
schema

If you need more explinations feel free to ask on email.

Posted in MSSQL.