0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fraz Zaki
  • 309
  • 2
  • 4
  • 16
  • 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

2 Answers2

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
gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52