How can I give condition on the column names in SQL Server 2008

Question

I have my database with the columns as follows:

keyword, part1_d1, part1_d2 ........ part1_d25, part2_d26, ......part2_d34

FYI: d1 through d34 are documents..

How can I give a query to obtain columns with column_name like '%part1%'; as below

keyword, part1_d1, part1_d2, ........ part1_d25 

I tried the query:

select (Select COLUMN_NAME 
          From INFORMATION_SCHEMA.COLUMNS
         where COLumn_NAME like '%part1%') 
       , keyword
  from sample

But it didn't work...

Please let me know what to do?

Answer 1

SQL doesn't support dynamic column names - you either have to explicitly state which ones you want, or use the asterisk "*" to indicate all the columns in the table.

Dynamic SQL would allow you to get a list of the columns, and create a SQL statement as a string before executing it - which is what you need to eventually use for the query you attempted. I recommend reading this--The Curse and Blessings of Dynamic SQL-- before looking further.

SQL Server 2005+:

DECLARE @sql NVARCHAR(4000)

   SET @sql = 'SELECT ' + STUFF((SELECT ', ' + x.column_name
                                   FROM INFORMATION_SCHEMA.COLUMNS x
                                   JOIN INFORMATION_SCHEMA.TABLES y ON y.object_id = x.object_id
                                  WHERE x.column_name LIKE '%part1%'
                                    AND y.table_name = 'sample'
                               GROUP BY x.column_name
                                FOR XML PATH ('')), 1, 2, '') + ' , keyword
                FROM sample '

BEGIN

  EXEC sp_executesql @sql

END