Database Challenges

-- File Name: niladri_biswas_tsqlchallenge_15_2.sql
-- Solution 2: With CTE
;WITH GET_ALL_REDORDS_CTE AS
(
	SELECT  Row,Col
	FROM  @Cols
	CROSS JOIN @Rows
)
, GET_MODULUS_CTE as
(
	SELECT	Row,Col,
			CASE WHEN ((Row % Col) = 0) THEN 0	ELSE 1 END AS [Mod Val]
	FROM GET_ALL_REDORDS_CTE
)

SELECT Row,
			 CASE WHEN	[1]= 0 THEN 'X' ELSE '' END [1]
			,CASE WHEN	[2]= 0 THEN 'X' ELSE '' END [2]
			,CASE WHEN	[3]= 0 THEN 'X' ELSE '' END [3]
			,CASE WHEN	[4]= 0 THEN 'X' ELSE '' END [4]
			,CASE WHEN	[5]= 0 THEN 'X' ELSE '' END [5]
			,CASE WHEN  [6]= 0 THEN 'X' ELSE '' END [6]
			,CASE WHEN  [7]= 0 THEN 'X' ELSE '' END [7]
			,CASE WHEN  [8]= 0 THEN 'X' ELSE '' END [8]
			,CASE WHEN  [9]= 0 THEN 'X' ELSE '' END [9]
FROM
(SELECT Row,Col,[Mod Val] FROM GET_MODULUS_CTE)AS X
PIVOT
(SUM([Mod Val]) FOR Col IN
([1],[2],[3],[4],[5],[6],[7],[8],[9]))AS 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.