How to Use Dynamic Column Names in an Update or Select Statement in a Function

How to use dynamic column names in an UPDATE statement in a function?

Some dynamic SQL will help. The select and update queries are taken from the question w/o change.

drop function if exists aa.clean_data();
create or replace function aa.clean_data() returns void language plpgsql as
$$
declare
    UPDATE_A constant text := $dynsql$ UPDATE autopipe.test SET %I = initcap(%I) $dynsql$;
    UPDATE_B constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '[^a-zA-Z\d\s:/\//]', '', 'g') $dynsql$;
    UPDATE_C constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '\s+$', '', 'g') $dynsql$;
    col_name text;
begin
    for col_name in select column_name from information_schema.columns where table_schema = 'aa' and table_name = 'test' and data_type in ('character varying', 'character','text', '"char"', 'name') loop
        execute format(UPDATE_A, col_name, col_name);
        execute format(UPDATE_B, col_name, col_name);
        execute format(UPDATE_C, col_name, col_name);
    end loop;
end;
$$;

It might be worth running a VACCUM on the target table after such a massive UPDATE.

How to use dynamic column names in an UPDATE or SELECT statement in a function?

In an UPDATE statement in PL/pgSQL, the table name has to be given as a literal. If you want to dynamically set the table name and the columns, you should use the EXECUTE command and paste the query string together:

EXECUTE 'UPDATE ' || quote_ident(r.relname) ||
       ' SET ' || quote_ident(r.cols_list[1]) || ' = $1, ' || 
                  quote_ident(r.cols_list[2]) || ' = $2' ||
       ' WHERE ' || quote_ident(r.cols_list[1]) || ' = $3 AND ' ||
                    quote_ident(r.cols_list[2]) || ' = $4'
USING ncicd9, ncdesc, ocicd9, ocdesc;

The USING clause can only be used for substituting data values, as shown above.

Applying a function to dynamic column names

You can do this with the help of INFORMATION_SCHEMA, STUFF and Dynamic SQL:

-- Get the all columns names from the underlying table
SELECT COLUMN_NAME
INTO #TEMP
FROM [Database_Name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'T' AND COLUMN_NAME != 'ID'

DECLARE @COLUMNS  NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

-- Construct a string with ISNULL
SELECT @COLUMNS =  STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(COLUMN_NAME) + ',0) ' + QUOTENAME(COLUMN_NAME) 
FROM #TEMP 
ORDER BY 1 
FOR XML PATH('')), 1, 1, '')

-- and use Dynamic SQL
SELECT @sql = 'SELECT ID,'+ @COLUMNS +' FROM T'

EXEC sp_executesql @sql

SQL SELECT statement with dynamic column names

You need some sort of case expression:

select t1.id,
       (case when t2.type = 'i1' then cast(val_int_1 as varchar(255))
             when t2.type = 'i2' then cast(val_int_2 as varchar(255))
             when t2.type = 's1' then val_string_1
             when t2.type = 's2' then val_string_2
        end) as val
from table1 t1 cross join
     table2 t2;

You are likely to complain "oh, I have so many columns". Basically, too bad. You have a poor database design. You are trying to do a partial match on strings and column names. Even a dynamic SQL solution is not very feasible.

SQL Server Query using a Dynamic Column Name

IS <> =, so you need to fix it :

SET @sql = 'select [' + @var1 + '] from [priority matrix] where impact  = ['+ @var2 + ']';

I would use QUOTENAME() instead of manual [] :

SET @sql = 'SELECT '+ QUOTENAME(@var1) +' FROM [priority matrix] WHERE impact  = '''+@var2+'''';

Literal strings are quoted with single quotes, [] are not required.

SQL: Select dynamic column name based on variable

EXEC ('SELECT ''value'' AS ' + @myDynamicColumn)

Dynamically column names from select sql

Is this what you were looking for:

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName) 
    from tempData
    group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = N'SELECT Name, ' + @cols + N' from 
 (
    select Name, value, ColumnName
    from tempData
) x
pivot 
(
    SUM(value)
    for ColumnName in (' + @cols + N')
) p '

exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.

how to select multiple column name dynamically In SQL Server

You should only use Dynamic SQL for this purpose.
like this :

declare @columns varchar(max) = 'Col1 , Col2 , Col3' ;    
declare @sql varchar(mx) = 'SELECT '+ @columns +'FROM emp' ;

---

Related Topics