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

Question

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?

When using the syntax `INSERT INTO ... EXEC...` the table most be defined prior to the insert. If your table has 12 columns, and the `EXEC` returns a dataset that only has 6 columns you need to define what columns to insert into in your `INSERT` clause: `INSERT INTO dbo.YourTable (Column1, Column2,..., Column6) EXEC...` — Thom A, Jun 17 '21 at 16:15
Yes, but the column names and column number are not fixed in the EXEC statement. It can sometimes return 10 or sometimes return 15 columns. The column names can also be anything. — Meen, Jun 17 '21 at 16:17
Why insert the result into a temp table instead of just returning it to the client? — David Browne - Microsoft, Jun 17 '21 at 16:19
Then I suggest a rethink of your requirements, @Meen , or you'll need to also create said table dynamically; a table must have a definition, or can't be "fluid" and have columns added/remove on it based on the data you want to `INSERT` into it. I suspect what you have may well be an [xy problem](http://xyproblem.info). — Thom A, Jun 17 '21 at 16:20
You might want to also consider returning data in a different way, and then making the presentation of the data... in the presentation layer, and not in the database. — James Z, Jun 17 '21 at 17:03
By the way, this line is wrong for a number of reason `SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS` Firstly you should use `QUOTENAME` on the column names, also you don't need `#Vals` at all, also you shouldn't use variable coalescing to aggregate values as it has issues. Instead this shoud be `SELECT @VALS = STRING_AGG(QUOTENAME(t.Questions), ',') FROM (SELECT DISTINCT [Questions] FROM Tbl_Eval) AS t` — Charlieface, Jun 17 '21 at 22:01

score 1 · Accepted Answer · answered Jun 18 '21 at 09:01

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

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

1 Answers1