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.
hi
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
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 ' ) ; '
