Hendrik Kleine Hendrik Kleine - 1 month ago 8
SQL Question

SQL - use CSV from variable in SELECT

I need to create a concatenation of multiple columns.

For example, if this would be my table:

IntA IntB IntC IntD
1 21 31 201601
2 35 88 201602


all columns are of datatype bigint, except IntD, which is always present and is Int.

I would like to calculate an 'CalculatedCol' column that will concatenate some of the columns as a string, like so;

cast(IntA as nvarchar(3)) + cast(IntB nvarchar(3)) + cast(IntD nvarchar(3))
AS CalculatedCol


Output being:

IntA IntB IntC IntD CalculatedCol
1 21 31 201601 121201601
2 35 88 201602 235201602


The tricky part is that the columns used in determining CalculatedCol are different (depending on which client is using the system). I get these columns from another table and have it stored in a variable as CSV string, i.e.:

@columns = IntA,IntB,IntD


This is where I'm stuck- is it possible to use this variable and take the columns it specifies between the commas? I'm not sure where to begin even.

I'm using SQL Server 2012.

Thanks in advance for your thoughts.

Answer

I'm not sure if dynamic SQL is what you want, but you can do:

declare @sql nvarchar(max);

set @sql = '
select ' + @columns + ', concat(' + @columns + ') as CalculatedCol
from t
';

exec sp_executesql @sql;
Comments