Sql: Select Dynamic Column Name Based on Variable

Using dynamic SQL to specify a column name by adding a variable to simple sql query

If you are trying to specify the column name dynamically, you could take a look at executing dynamic sql. However, you should make sure to read about the dangers of this approach first:

http://www.sommarskog.se/dynamic_sql.html

From that page, there is a sample that shows dynamically specifying the table name -- you could change it so it dynamically specifies the column name instead:

CREATE PROCEDURE general_select @tblname nvarchar(128),
                                @key     varchar(10),
                                @debug   bit = 0 AS
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT col1, col2, col3
            FROM dbo.' + quotename(@tblname) + '
            WHERE keycol = @key'
IF @debug = 1 PRINT @sql
EXEC sp_executesql @sql, N'@key varchar(10)', @key = @key

So for example if you had a table 'MyTable' with columns named 'x', 'y', and 'z', it might look like:

DECLARE @columnName nvarchar(128)
DECLARE @sql nvarchar(4000)
set @columnName = 'z'

SET @sql = 'SELECT x, y, ' + @columnName + ' from MyTable'
EXEC sp_executesql @sql, N'@columnName varchar(128)', @columnName = @columnName

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.

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.

SQL: Select dynamic column name based on variable

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

How to pass column name as variable in dynamic query in SQL Server?

This should be what you are after. There's a few comments in the SQL you need to pay attention to:

DECLARE @SQL nvarchar(max),
        @tname sysname, --note the datatype change
        @cname sysname, --note the datatype change
        @acc_num nvarchar(50),
        @dp_code nvarchar(100); --This is never used in your sample query
SET  @cname = 'column_name';
SET @acc_num = 'xyz';
SET @tname = 'table_name';


SET @SQL = N'SELECT ' + NCHAR(13) + NCHAR(10) +
             STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
                           N'       ' + N'COUNT DISTINCT(' + QUOTENAME(C.Column_Name) + N') AS ' + QUOTENAME(C.Column_Name)
                    FROM INFORMATION_SCHEMA.COLUMNS C
                    WHERE C.Table_Name = @tname
                    ORDER BY C.ORDINAL_POSITION
                    FOR XML PATH(N'')),1,10,N'') + NCHAR(13) + NCHAR(10) +
             N'FROM ' + QUOTENAME(@tname) + NCHAR(13) + NCHAR(10) +
             N'WHERE ' + QUOTENAME(@cname) + N' = @Acc_Num;'

PRINT @SQL; --YOur debugging best friend
EXEC sp_executesql @SQL, N'@Acc_Num varchar(100)',@Acc_Num = @acc_num;

This parametrised the query instead, using sp_executesql, and nicely formats it for your PRINT statement.

If you don't understand then please do ask.

SQL Server : dynamically select column based on another select column

You need to create a dynamic unpivot statement and execute it.

Try not to get confused about what is the static and what the dynamic parts.

DECLARE @sql nvarchar(max) = N'
SELECT v.*
FROM (
    SELECT TOP (1) *
    FROM ' + QUOTENAME(@tablename) + N'
    ORDER BY CHECKSUM(NEWID())
) t
CROSS APPLY (VALUES
' +
(
    SELECT STRING_AGG(CAST(
N'(' + QUOTENAME(c.name, '''') + N', CAST(t.' + QUOTENAME(c.name) + N' AS sql_variant))'
        AS nvarchar(max)), N',
') WITHIN GROUP (ORDER BY c.name ASC)
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@tablename)
) +
N'
) AS v(columnName, columnValue);
';

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;

---

If you are unfortunate enough to still be on a version not supporting STRING_AGG, you can use FOR XML instead for that part:

-------
CROSS APPLY (VALUES
' +
STUFF(
(
    SELECT N',
(' + QUOTENAME(c.name, '''') + N', CAST(t.' + QUOTENAME(c.name) + N' AS sql_variant))'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@tablename)
    ORDER BY c.name ASC
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, 2, '') +
N'
) AS v(columnName, columnValue);
';
--------

dynamic column name based on integer variable

For an alias like that, you'll need to wrap it in double quotes or square brackets, like so:

declare @colDate1 char(8)
set @colDate1 = '1'
 EXEC ('SELECT ''value'' AS [' + @colDate1 + ']')

For dynamic aliasing, I'd suggest just always doing it this way, just to be safe.

---

Related Topics