Database Challenges

-- File Name: Ben_Flanaghan_tsqlchallenge_15.sql
--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 @Columns. 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: 
--
--col         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 

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


select Row as col, [1], [2], [3], [4], [5], [6], [7], [8], [9]
from (select Row, case Row%Col when 0 then 'x' else ' ' end as Val, Col from @Cols cross join @Rows) p
PIVOT (max(Val) for Col in ([1], [2], [3], [4], [5], [6], [7], [8], [9])) pvt

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.