Database Challenges

-- File Name: kevan_riley_tsqlchallenge_15.sql
--
-- Kevan Riley 
-- TSQL Challenge #15 - matching data between rows and columns
--
-- create a cartesian product of rows x cols using a cross join
-- and pivot the result
--
-- this solution will work for any number of rows

select
	row, [1],[2],[3],[4],[5],[6],[7],[8],[9]
from
(select
	col,
	row, 
	case when row%col = 0 then 'X' else '' end as coord
	from @cols cross join @rows) as Sourcetable
PIVOT
(
	max(coord)
FOR col in ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) as PIVOTTABLE

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.