现有一表如下EMPID B1 B2 B3 B4 B5 1123 A A A A A如何通过语句创建一新表,结构如下B1 1123 A B2 1123 A B3 1123 A B4 1123 A B5 1123 A ------------------------------ 我写个笨方法 select B='B1',EMPID,B1 from 表 union all select 'B2',EMPID,B2 from 表 union all select 'B3',EMPID,B3 from 表 union all select 'B4',EMPID,B4 from 表 union all select 'B5',EMPID,B5 from 表
create table a (EMPID varchar(10),B1 VARCHAR(10),B2 VARCHAR(10),B3 VARCHAR(10),B4 VARCHAR(10),B5 VARCHAR(10),) insert into a select '1123','A','A','A','A','A'-----测试数据declare @sql varchar(4000) set @sql='' select @sql=@sql+'union all select EMPID,'+name+' as l,name='''+name+''' from a ' from dbo.syscolumns b where id=object_id('a') and name!='empid' select @sql=stuff(@sql,1,9,'') exec (@sql)
1123 A A A A A如何通过语句创建一新表,结构如下B1 1123 A
B2 1123 A
B3 1123 A
B4 1123 A
B5 1123 A
------------------------------
我写个笨方法
select B='B1',EMPID,B1 from 表
union all
select 'B2',EMPID,B2 from 表
union all
select 'B3',EMPID,B3 from 表
union all
select 'B4',EMPID,B4 from 表
union all
select 'B5',EMPID,B5 from 表
insert into a select '1123','A','A','A','A','A'-----测试数据declare
@sql varchar(4000)
set @sql=''
select @sql=@sql+'union all select EMPID,'+name+' as l,name='''+name+''' from a '
from dbo.syscolumns b
where id=object_id('a') and name!='empid'
select @sql=stuff(@sql,1,9,'')
exec (@sql)