Dynamically add columns in SQL Server

Question

I have a variable in SQL Server:

DECLARE @cols AS NVARCHAR(MAX);

Variable @cols contains these values:

[Afghanistan],[Australia],[Bangladesh]

Somewhere in a stored procedure, I have this SQL statement:

SELECT ID, AccountTypeName AS Type 
FROM cte AS t

I want this SQL satement to execute with the column names stored in @cols - like this

SELECT ID, AccountTypeName, [Afghanistan], [Australia], [Bangladesh],[England] AS Type 
FROM cte AS t

My problem is I am using a CTE.

You can do it olny using dynamic sql; cte should be defined inside this dynamic code — sepupic, Nov 02 '17 at 11:52
Possible duplicate of [Dynamically create columns sql](https://stackoverflow.com/questions/12643117/dynamically-create-columns-sql) — DrHouseofSQL, Nov 02 '17 at 11:56

score 0 · Answer 1 · answered Nov 02 '17 at 11:52

0

You can use dynamic T-SQL statement:

DECLARE @DynamicTSQLStatement NVARCHAR(MAX) = N'SELECT ID,AccountTypeName, ' + @cols + ' as Type from cte as t'

EXEC sp_executesql @DynamicTSQLStatement

answered Nov 02 '17 at 11:52

gotqn

37,902
44
152
231

Yogesh Sharma · Answer 2 · 2017-11-02T12:36:46.807

However use Print command to check what Query looks

Complete Dynamic T-SQL Query Approach is here

DECLARE @cols NVARCHAR(MAX), @Query NVARCHAR(MAX);

SET @cols = '[Afghanistan],[Australia],[Bangladesh]';

SET @Query = N';WITH CTE AS (SELECT NULL [DATA]) SELECT [DATA], '+@cols+' FROM CTE';

PRINT @Query;

EXEC sp_executesql @Query;

How could u bound the column which is not part of CTE ? & why ?

Other way

However you could bind the Dynamic column directly in CTE as below :

SET @cols = 'NULL [Afghanistan],NULL [Australia],NULL [Bangladesh]';

SET @Query = N';WITH CTE AS (SELECT NULL [DATA], '+@cols+') SELECT * FROM CTE';

@Query result :

;WITH CTE AS (SELECT NULL [DATA], NULL [Afghanistan],NULL [Australia],NULL [Bangladesh]) SELECT * FROM CTE

EXEC sp_executesql @Query result :

DATA    Afghanistan Australia   Bangladesh
NULL    NULL        NULL        NULL

Dynamically add columns in SQL Server

2 Answers2