Insert only few columns from exec (SQL) when column name is not fixed

I have the below code:

IF OBJECT_ID(N'tempdb..#VALS') IS NOT NULL
BEGIN
DROP TABLE #VALS
END
IF OBJECT_ID(N'tempdb..#Tbl_Eval_Temp') IS NOT NULL
BEGIN
DROP TABLE #Tbl_Eval_Temp
END
IF exists (select top 1 * from Tbl_Eval)
BEGIN
DROP TABLE Tbl_Eval
END
create table Tbl_Eval (EmpID INT,   CourseID NVARCHAR(100), Questions NVARCHAR(MAX),    Answers NVARCHAR(MAX))
Insert into Tbl_Eval Values 
(1000,  100,    1   ,'2'),
(1000,  100,    2   ,'4'),
(1000,  100,    3   ,'1'),
(1000,  100,    4   ,'3'),
(1000,  100,    5   ,'5'),
(1021,  100,    1   ,'4'),
(1021,  100,    2   ,'3'),
(1021,  100,    3   ,'5'),
(1021,  100,    4   ,'1'),
(1021,  100,    5   ,'3'),
(1021,  150,    1   ,'Good'),
(1021,  150,    2   ,'Very Good'),
(1021,  150,    3   ,'Average'),
(1021,  150,    4   ,'Poor'),
(1021,  150,    5   ,'Very Poor'),
(1021,  150,    6   ,'Good'),
(1021,  150,    7   ,'Very Good'),
(1021,  150,    8   ,'Average'),
(1021,  150,    9   ,'Poor'),
(1021,  150,    10  ,'Very Poor')

DECLARE @SQL NVARCHAR(MAX)
DECLARE @VALS NVARCHAR(500)
SELECT DISTINCT [Questions] AS VALS INTO #VALS FROM Tbl_Eval
SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS
create table #Tbl_Eval_Temp (EmpID INT, CourseID NVARCHAR(100), Q1 NVARCHAR(100),Q2 NVARCHAR(100),Q3 NVARCHAR(100),Q4 NVARCHAR(100),Q5 NVARCHAR(100),Q6 NVARCHAR(100),Q7 NVARCHAR(100),Q8 NVARCHAR(100),Q9 NVARCHAR(100),Q10 NVARCHAR(100)  )

SET @SQL = 'SELECT EmpID, CourseID,'+@VALS+' FROM Tbl_Eval
           PIVOT(MIN([Answers]) FOR [Questions] IN ('+@VALS+')) PIV'
PRINT @SQL
INSERT INTO #Tbl_Eval_Temp EXEC(@SQL)
SELECT * FROM #Tbl_Eval_Temp

The output is:

I know that EXEC(SQL) will give me 12 columns, so i created the temporary table #Tbl_Eval_Temp with 12 columns. What if the number of columns and the name of the columns is also getting generated at run time. There could be only 5 or 15 columns. So, how can I still achieve the desired output as shown in the pic?

Answer

You can use exec sp_executesql like this code that I coded it for you :

DROP TABLE  IF EXISTS TBL_Eval
DROP TABLE IF EXISTS Tbl_Eval_Temp

create table Tbl_Eval (EmpID INT,   CourseID NVARCHAR(100), Questions NVARCHAR(MAX),    Answers NVARCHAR(MAX))
Insert into Tbl_Eval Values 
(1000,  100,    1   ,'2'),
(1000,  100,    2   ,'4'),
(1000,  100,    3   ,'1'),
(1000,  100,    4   ,'3'),
(1000,  100,    5   ,'5'),
(1021,  100,    1   ,'4'),
(1021,  100,    2   ,'3'),
(1021,  100,    3   ,'5'),
(1021,  100,    4   ,'1'),
(1021,  100,    5   ,'3'),
(1021,  150,    1   ,'Good'),
(1021,  150,    2   ,'Very Good'),
(1021,  150,    3   ,'Average'),
(1021,  150,    4   ,'Poor'),
(1021,  150,    5   ,'Very Poor'),
(1021,  150,    6   ,'Good'),
(1021,  150,    7   ,'Very Good'),
(1021,  150,    8   ,'Average'),
(1021,  150,    9   ,'Poor'),
(1021,  150,    10  ,'Very Poor'),
(1021,  100,    11  ,'Very Good')

DECLARE @SQL NVARCHAR(MAX)
DECLARE @VALS NVARCHAR(500)
SELECT DISTINCT [Questions] AS VALS INTO #VALS FROM Tbl_Eval
SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS
DECLARE @Counter AS INT
DECLARE @DistinctCount AS INT
DECLARE @CreateTableSql AS NVARCHAR(4000)
SET @Counter = 1
SET @DistinctCount = (SELECT MAX(CONVERT(INT,Vals)) FROM #VALS)
SET @CreateTableSql = 'create table Tbl_Eval_Temp (EmpID INT, CourseID NVARCHAR(100) '
WHILE(@Counter<=@DistinctCount)
BEGIN
    
    SET @CreateTableSql += CONCAT(', Q',@Counter, ' NVARCHAR(100)')
    SET @Counter+=1;
END
SET @CreateTableSql+=')'
PRINT @CreateTableSql

EXEC sp_executesql @CreateTableSql


SET @SQL = 'SELECT EmpID, CourseID,'+@VALS+' FROM Tbl_Eval
           PIVOT(MIN([Answers]) FOR [Questions] IN ('+@VALS+')) PIV'
PRINT @SQL
INSERT INTO Tbl_Eval_Temp EXEC(@SQL)
SELECT * FROM Tbl_Eval_Temp
DROP TABLE #VALS