SQLTeam.com | Weblogs | Forums

Choose the different columns from the table in sql

I have a table 200 columns. The titles of the columns are "value_1", "X_1", "value_2", X_2",.... "value_98", "X_98". I want to build a table which only includes the "value_i".
Can you help me with that? Thank you.

Note: The table is veryyyy very big and I can not share it here.


hope this helps

you will have to remove the last column comma manually .. it can be done from script also

drop table temp 

create table temp 
   value_1 int ,
   name int , 
   age int , 
   value_2 varchar(100),
   place varchar(50) 

select 'create table abc (' 
union all  
    name,' ',
    case system_type_id 
        when 167 then 'varchar('+cast(max_length as varchar(10))+'), ' 
        when 106 then 'decimal('+cast(precision as varchar(10))+','+cast(scale as varchar(10))+'), '
        when 56 then 'int, ' 
        --add more case for more datatype
    else '' end
From sys.columns 
where object_id=(select object_id from sys.tables where name='temp')
and name like '%value%'
union all  
select ' ) ; '