一个交叉表.用字段明做为值:原表数据为:
字段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
