Hendrik Kleine Hendrik Kleine - 1 year ago 71
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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download