SQL Puzzle: SQL Advance Query – Using Recursive CTE, Generates the Columns

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output for generating new columns using Recursive CTE.

Input Data:

a1 a2 a3 a4 a5 a6

----------- ----------- ----------- ----------- ----------- -----------

1 2 3 4 5 6

Expected Output:

a1 a2 a3 a4 a5 a6 b1 b2 b3 b4 b5 b6

------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

1 2 3 4 5 6 2 4 6 8 10 12

Solution:

WITH

col1(a1, a2, a3, a4, a5, a6) AS (SELECT 1,2,3,4,5,6),

col2(b1, b2, b3, b4, b5, b6) AS

(

SELECT

a1 * 2

,a2 * 2

,a3 * 2

,a4 * 2

,a5 * 2

,a6 * 2

FROM col1

)

SELECT *

FROM col1, col2

Please try the different solution for this puzzle and share it via comment...

Feb 18, 2018Anvesh Patel