TSQL Challenge 15 - matching data between rows and columns

This challenge is derived from a realistic reporting scenario that requires the transformation of rows into columns. This is commonly known as a matrix, pivot, or crosstab query. This challenge involves two primary tables @Row and @Cols. The objective will be to create a matrix result set. Before I explain the rest of the requirements, let me show you the sample tables. Column Table:

Col
-----------
1
2
3
4
5
6
7
8
9

Row Table:

Row
-----------
100
104
101
99
77
20
10

The final output should look like this:

Row         1    2    3    4    5    6    7    8    9
----------- ---- ---- ---- ---- ---- ---- ---- ---- ----
10          x    x              x                   
20          x    x         x    x                   
77          x                             x         
99          x         x                             x
100         x    x         x    x                   
101         x                                       
104         x    x         x                   x    

The challenge is to mark a coordinate, with a value of X, if and only if the row value is divisible by the col value, i.e. it has a modulo of zero. The additional requirements are: the final query must work with random row values and the pivot operator should be used.

Sample Data

Use the following script to generate the sample data needed for this challenge

DECLARE @Cols TABLE(Col INT);

INSERT INTO @Cols VALUES (1);
INSERT INTO @Cols VALUES (2);
INSERT INTO @Cols VALUES (3);
INSERT INTO @Cols VALUES (4);
INSERT INTO @Cols VALUES (5);
INSERT INTO @Cols VALUES (6);
INSERT INTO @Cols VALUES (7);
INSERT INTO @Cols VALUES (8);
INSERT INTO @Cols VALUES (9);

DECLARE @Rows TABLE(Row INT);

INSERT INTO @Rows VALUES (100);
INSERT INTO @Rows VALUES (104);
INSERT INTO @Rows VALUES (101);
INSERT INTO @Rows VALUES (99);
INSERT INTO @Rows VALUES (77);
INSERT INTO @Rows VALUES (20);
INSERT INTO @Rows VALUES (10);

Challenge Requirements Summary

1. Only coordinates where the column value modulo the row value equals zero should be marked, with an "X"
2. Number of rows in the table is not fixed. The query should work with variable number of rows in the table.

Notes

1. Read the Submission Guidelines and make sure that your solution follows them.
2. The solution should work on SQL Server 2005, 2008 or later versions
3. Use this forum for any questions related to TSQL Challenge #15

About the Authors.

Related Posts

Comments