Hello,
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.
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
select
concat(
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 ' ) ; '