locked
how to select multiple column name dynamically RRS feed

  • Question

  • User-1634604574 posted

    i want to select multiple column dynamically at once

    Wednesday, January 30, 2019 2:17 PM

Answers

  • User-893317190 posted

    Hi zhyanadil.it@gmail.com,

    About how to select all the column names , you could use the system table sys.columns to help.

    For example , I have a table named dbo.customer.

    id	name	       age
    12	customer1	30
    13	customer2	23
    14	customer4	18
    15	customer5	33

    The you could use the sys.columns table to select all the column of the table, please pass your table name to the function object_id

      select [name] as colname from sys.columns where object_id = object_id('dbo.customer'))

    The result.

    colname
    id name age

    But you should change the rows to a column to select them in your dynamic query, you could use the query below.

      select  STUFF(( SELECT    ',[' + colname +']'
                              FROM    ( select [name] as colname from sys.columns where object_id = object_id('dbo.customer') )  as cte                    --cte
                              
                                FOR
                              XML PATH('')
                            ), 1, 1, '') 

    The result.

    [id],[name],[age]

    select ',[' + colname+']'   from tablename for xml path('')  could get every row of your table and combine them together into one row.

    So, what it does is like  ',['+'id'+']' +',['+'name'+']'',['+'age'+']' , finally you could see the result  ,[id],[name],[age],

    Stuff is used to remove the first ','.

    Then just set  a variable to the final result and use exec to execute the sql.

    declare @column nvarchar(200);
    select  @column = STUFF(( SELECT    ',[' + colname +']'
                              FROM    ( select [name] as colname from sys.columns where object_id = object_id('dbo.customer') )  as cte                    --cte
                              
                            FOR
                              XML PATH('')
                            ), 1, 1, '')  
    
    	
    exec( 'select '+ @column +' from dbo.customer') -- write your table name

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 31, 2019 3:24 AM

All replies

  • User475983607 posted

    zhyanadil.it@gmail.com

    i want to select multiple column dynamically at once

    The * means select all columns.

    Select * 
    FROM SomeTable

    https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-2017

    Wednesday, January 30, 2019 2:23 PM
  • User-1634604574 posted

    not like that

    i want this 

    select @column_name from table1

    by that code i want to select multiple column it mean dynamically put column name into that variable @column

    Wednesday, January 30, 2019 2:48 PM
  • User475983607 posted

    not like that

    i want this 

    select @column_name from table1

    by that code i want to select multiple column it mean dynamically put column name into that variable @column

    Craft a dynamic query.  Basically string concatenation then use sp_executesql() to invoke the query.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017

    Wednesday, January 30, 2019 2:59 PM
  • User753101303 posted

    Hi,

    You can't this way (@variable is to replace only literal values). Maybe dynamic SQL but as often the intent could help. For example if the goal is to customize a table display or if this is a security concern or any other reason for which you may need to get this kind of variation, you won' necessrily use the same approach.

    Wednesday, January 30, 2019 3:45 PM
  • User-893317190 posted

    Hi zhyanadil.it@gmail.com,

    About how to select all the column names , you could use the system table sys.columns to help.

    For example , I have a table named dbo.customer.

    id	name	       age
    12	customer1	30
    13	customer2	23
    14	customer4	18
    15	customer5	33

    The you could use the sys.columns table to select all the column of the table, please pass your table name to the function object_id

      select [name] as colname from sys.columns where object_id = object_id('dbo.customer'))

    The result.

    colname
    id name age

    But you should change the rows to a column to select them in your dynamic query, you could use the query below.

      select  STUFF(( SELECT    ',[' + colname +']'
                              FROM    ( select [name] as colname from sys.columns where object_id = object_id('dbo.customer') )  as cte                    --cte
                              
                                FOR
                              XML PATH('')
                            ), 1, 1, '') 

    The result.

    [id],[name],[age]

    select ',[' + colname+']'   from tablename for xml path('')  could get every row of your table and combine them together into one row.

    So, what it does is like  ',['+'id'+']' +',['+'name'+']'',['+'age'+']' , finally you could see the result  ,[id],[name],[age],

    Stuff is used to remove the first ','.

    Then just set  a variable to the final result and use exec to execute the sql.

    declare @column nvarchar(200);
    select  @column = STUFF(( SELECT    ',[' + colname +']'
                              FROM    ( select [name] as colname from sys.columns where object_id = object_id('dbo.customer') )  as cte                    --cte
                              
                            FOR
                              XML PATH('')
                            ), 1, 1, '')  
    
    	
    exec( 'select '+ @column +' from dbo.customer') -- write your table name

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 31, 2019 3:24 AM