CTE creating recursive dynamic rows and columns based on input

Question

I am creating dynamic rows and columns based on input from user. The output is similar to this.

So far I could able to compute only one column using CTE.

    DECLARE @ROWStart INT = 1
DECLARE @NOOFROWS INT = 5
DECLARE @NOOFOLUMNS INT = 5
DECLARE @RACK NVARCHAR(10) = 'TAB-'
;WITH   cte
AS     (SELECT @ROWStart AS n, 1 AS col,CONVERT(VARCHAR(20),@RACK + 'R1C1') AS RW -- anchor member
        UNION ALL
        SELECT n + 1,cte.col, CONVERT(VARCHAR(20),@RACK + 'R' + CONVERT(VARCHAR(10),n+1) + 'C' + CONVERT(VARCHAR(10),col))  -- recursive member
        FROM   cte
        WHERE  n < @NOOFROWS -- terminator
       )
SELECT cte.RW
FROM   cte;

How to create subsequent columns?

Answer 1

Try this:

DECLARE @RowsLimit INT
       ,@columnsLimit INT;

SELECT @RowsLimit = 5
      ,@columnsLimit = 7;

DECLARE @DynammicTSQLStatement NVARCHAR(MAX)
       ,@DynamicPIVOTColumns NVARCHAR(MAX)
       ,@DynamicPIVOTResultsColumns NVARCHAR(MAX);

CREATE TABLE #Rows
(
    [value] INT
);

CREATE TABLE #Colmuns
(
    [RowID] INT
   ,[value] INT
);

INSERT INTO #Rows ([value])
SELECT TOP (@RowsLimit) + ROW_NUMBER() OVER(ORDER BY t1.[number])
FROM [master]..[spt_values] t1 
CROSS JOIN [master]..[spt_values] t2

INSERT INTO #Colmuns ([RowID], [value])
SELECT TOP (@columnsLimit)  ROW_NUMBER() OVER(ORDER BY t1.[number]) 
                           ,ROW_NUMBER() OVER(ORDER BY t1.[number])
FROM [master]..[spt_values] t1 
CROSS JOIN [master]..[spt_values] t2

SET @DynamicPIVOTResultsColumns = STUFF
                          (
                                (
                                SELECT ',REPLACE(''TAB-R#C' + CAST([value] AS VARCHAR(12)) + ''', ''#'', [value])'
                                FROM #Colmuns
                                GROUP BY [value]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );

SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',[' + CAST([value] AS VARCHAR(12)) + ']'
                                FROM #Colmuns
                                GROUP BY [value]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );


SET @DynammicTSQLStatement = N'
SELECT '+ @DynamicPIVOTResultsColumns +'
FROM #Rows
CROSS APPLY 
(
    SELECT *
    FROM #Colmuns
    PIVOT
    (
        MAX([value]) FOR [RowID] IN (' + @DynamicPIVOTColumns + ')
    ) PVT
) DS';


EXEC sp_executesql @DynammicTSQLStatement;


DROP TABLE #Rows;
DROP TABLE #Colmuns;