SQL - SELECT a column builded with the value from other column-CodePudding

I want to solect a column in a dynamic way, it will be the composition of a static string the value from another column:

SELECT QUOTENAME(CONCAT('static_string', T1.value)

FROM TABLE1 AS T1

= Returns the string 'static_string' value

*Having Table1 a column called 'static_string' value

How can I solve this? I tried with EXEC, but I cannot set the value due to each row have a different one.

SQL server v18

CodePudding user response：

In your example you are only missing a closing bracket.

SELECT QUOTENAME(CONCAT('static_string', T1.value))
FROM TABLE1 AS T1

CodePudding user response：

Looks like you need to dynamically create the list of columns you are looking for and then query the table for that specific list of columns.

Not sure what design decisions brought you to this exact spot, because it is not a great one to be in - generally, running dynamic SQL requires elevated permissions (more privileges you'd typically want to assign to an application user) so I would proceed with caution.

However, technically, this can be done of course and one way of doing it would be the following:

-- prepare the stage
create table test(
    value varchar(50),
    static_stringvalue1 int,
    static_stringvalue2 int,
    static_stringvalue3 int
);

insert into test (value, static_stringvalue1) VALUES ('value1', 10);
insert into test (value, static_stringvalue2) VALUES ('value2', 20);
insert into test (value, static_stringvalue3) VALUES ('value3', 30);

select * from test;

-- run the show :D 
declare @sql nvarchar(500), @column_names varchar(500)

-- building a comma separated list out of the list of columns we need
set @column_names = (select string_agg(QUOTENAME(CONCAT('static_string', t.value)), ',') as col_names from test t);

set @sql = 'select @column_names as whatever from test;'

-- before exeucting, you can print both values to see what will you be executing
print(@column_names)
print(@sql)
exec sp_executesql @sql, N'@column_names varchar(500)', @column_names

A working version of this can be found here: fiddle example.