IDC资讯中心
一个交叉表.用字段明做为值:原表数据为:
字段1 字段2 字段3
a1 b1 c1
a2 b2 c2
变换后:
col1 col2 col3
字段1 a1 a2
字段2 b1 b2
字段3 c1 c2
create table tablename (字段1 varchar(100),字段2 varchar(100),字段3 varchar(100))
insert tablename select gsm900/1800mhz/gprs, 85*44*21mm, 80
union all select gsm900/1800mhz/gprs ,82*46*21.5mm, 79
go
select top 0
字段名=a.name
into abc
from syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype=u and d.name = tablename
order by a.id,a.colorder
select top 0
note =a.name
into abcd
from syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype=u –表示用户类型
and d.name = tablename — 需要获取字段的表
order by a.id,a.colorder
declare @num int , @col varchar(50) , @name varchar(200), @name1 varchar(200) , @count int , @num1 varchar(5)
select @num = 1
select @count = count(*) from tablename
while (@count > 0)
begin
select @col = col + convert(varchar, @num)
exec(alter table abc add [ + @col + ] varchar(200) null )
select @num = @num +1
set @count = @count – 1
end
declare addnamecolumns_cursor insensitive cursor –取字段值
for
select
filedname = a.name
from syscolumns a
inner join sysobjects d on a.id=d.id
left join sysproperties f on d.id=f.id and f.smallid=0
where d.xtype=u and d.name = tablename
order by a.id,a.colorder
open addnamecolumns_cursor
begin
fetch next from addnamecolumns_cursor into @name –对应相应字段值
while @@fetch_status = 0
begin
insert into abc (字段名)
select fname = @name
delete abcd
exec ( insert into abcd (note) select name = [ + @name +] from tablename )
select @num = 1
declare addnamecolumns_cursor1 insensitive cursor
for select note from abcd
open addnamecolumns_cursor1
begin
fetch next from addnamecolumns_cursor1 into @name1
while @@fetch_status = 0
begin
select @num1 = convert(varchar, @num)
exec(update abc set [col+ @num1 + ] = + @name1+ where 字段名=+ @name +)
print update abc set [ + @col + ] = + @name1+ where 字段名=+ @name +
select @num = @num +1
fetch next from addnamecolumns_cursor1 into @name1
end
end
close addnamecolumns_cursor1
deallocate addnamecolumns_cursor1
fetch next from addnamecolumns_cursor into @name
end
end
close addnamecolumns_cursor
deallocate addnamecolumns_cursor
select * from abc
select * from tablename
drop table tablename
drop table abc
drop table abcd
