Pages

Sunday, November 8, 2009

Matching Data Between Rows and Columns

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)

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

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

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.
Row         1    2    3    4    5    6    7    8    9   Total
----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
10 x x x 3
20 x x x x 4
77 x x 2
99 x x x 3
100 x x x x 4
101 x 1
104 x x x x 4
----------------------------------------------------------------
Total 7 4 1 3 3 0 1 1 1 21
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.

0 comments:

Post a Comment