none
Select columns based on patterns within the column names

    Question

  • In SQL 2005 is there a way to select columns based on patterns within the column names?  For example I have a view with many columns in it but I want to create a dataset that only contains a few of these columns.  The columns I'm interested in have a common and unique naming convention.  So if my columns were as follows:

    table.columngroup1.colum1
    table.columngroup1.colum2
    table.columngroup1.colum3
    table.columngroup2.colum1
    table.columngroup2.colum2
    table.columngroup3.colum1
    table.columngroup3.colum2
    table.columngroup3.colum3

    I'm interested in seleting only the columns from columngroup1.

    Thanks,
    Scott
    Sunday, August 16, 2009 2:14 PM

Answers

  • Basially what you want cannot be done directly. You have to use Dynamic SQL.

    The link is about Dynamic SQL not about your scenario. You need to understand the advantages and disadvantages about Dynamic SQL before going into it. Dynamic SQL will do what you want, but is it worth the effort to go the Dynamic SQL way just to do what you want here ? When you can simply type in the required column name in the SELECT statement ? ?

    --------------------------------------------------------------------------------------------------------

    You have to get the column name from the INFORMATION_SCHEMA.COLUMNS

    SELECT COLUMN_NAME
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'yourtable'
    AND      LEFT(COLUMN_NAME, 12) = 'yourcolumngroup'

    and then form the select statement dynamically.

    something like

    declare @sql nvarchar(max),
               @col nvarchar(max)
    
    SELECT @col = isnull(@col + ',', '') + COLUMN_NAME
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'yourtable'
    
    select @sql = 'SELECT ' + @col + ' FROM yourtable'
    
    exec (sql)


    KH Tan
    • Marked as answer by Sakendrick Tuesday, August 18, 2009 1:32 PM
    Monday, August 17, 2009 4:41 AM

All replies

  • One thing to clarify... the columns in any given group are dynamic, which is why I'm not just explicitly selecting the 3 columngroup1 columns.
    Sunday, August 16, 2009 3:20 PM
  • then you will required to use Dynamic SQL. See http://www.sommarskog.se/dynamic_sql.html

    Query INFORMATION_SCHEMA.COLUMNS for the column name of the table and form your query dynamically
    KH Tan
    Sunday, August 16, 2009 3:48 PM
  • Browsed through there and couldn't find my scenario... I guess what I'm looking for is someway to fill a variable/array with column names based on a function, then use that for my select.  For example

    pseudo code:

    from table get columns where left(column name, 12) = columngroup1
    select @abovelist from table

    Monday, August 17, 2009 3:02 AM
  • Basially what you want cannot be done directly. You have to use Dynamic SQL.

    The link is about Dynamic SQL not about your scenario. You need to understand the advantages and disadvantages about Dynamic SQL before going into it. Dynamic SQL will do what you want, but is it worth the effort to go the Dynamic SQL way just to do what you want here ? When you can simply type in the required column name in the SELECT statement ? ?

    --------------------------------------------------------------------------------------------------------

    You have to get the column name from the INFORMATION_SCHEMA.COLUMNS

    SELECT COLUMN_NAME
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'yourtable'
    AND      LEFT(COLUMN_NAME, 12) = 'yourcolumngroup'

    and then form the select statement dynamically.

    something like

    declare @sql nvarchar(max),
               @col nvarchar(max)
    
    SELECT @col = isnull(@col + ',', '') + COLUMN_NAME
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'yourtable'
    
    select @sql = 'SELECT ' + @col + ' FROM yourtable'
    
    exec (sql)


    KH Tan
    • Marked as answer by Sakendrick Tuesday, August 18, 2009 1:32 PM
    Monday, August 17, 2009 4:41 AM
  • I managed to build the table I was looking for with your suggestion and some minor edits as follows:


    declare @sql nvarchar(max),
               @col nvarchar(max)
    
    SELECT @col = isnull(@col + ',', '') + '[' + COLUMN_NAME +']'
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myview'
    AND      LEFT(COLUMN_NAME, 12) = 'mycolumn'
    select @sql = 'SELECT ' + @col + ' FROM myview' exec (@sql)
    Now I just have to figure out why it's not building a dataset for my report.  I was doing this to pull specific columns from a view for a dataset for a report in reporting services.  Need it to be dynamic because this report needs to work for more than one customer.  However in RS, while the dataset executes fine and gets me the data, none of the data is showing up in my report.  I'll post something else in the Reporting Services forum.


    Thanks for the tips!
    Scott
    Tuesday, August 18, 2009 1:32 PM