SQL - use CSV from variable in SELECT

Question

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.

Gordon Linoff · Accepted Answer · 2016-10-26 15:27:25Z

2

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;

answered Oct 26, 2016 at 15:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hendrik Kleine Over a year ago

that work like a charm. Dynamic is fine in my use case. thanks.

Collectives™ on Stack Overflow

SQL - use CSV from variable in SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related