麻烦各位了有原始表这样:
table: aaaid1, id2, id3, m1, m2, m3
a, a, a am1,am2,am3想转换成这样
id1, id2, id3, m, val
a, a, a, m1, am1
a, a, a, m2, am2
a, a, a, m3, am3原始表中除了id1, id2, id3其他的列都是不固定的,( 列的数目和名字都不固定), 请各位指点,谢谢了
table: aaaid1, id2, id3, m1, m2, m3
a, a, a am1,am2,am3想转换成这样
id1, id2, id3, m, val
a, a, a, m1, am1
a, a, a, m2, am2
a, a, a, m3, am3原始表中除了id1, id2, id3其他的列都是不固定的,( 列的数目和名字都不固定), 请各位指点,谢谢了
insert into aaa select 'a','a','a','am1','am2','am3'declare @s varchar(8000)
set @s=''
select
@s=@s+' union all select id1,id2,id3,'''+name+''' as m,val='+name+' from aaa'
from syscolumns where id=object_id('aaa') and name not in('id1','id2','id3') order by colid
set @s=stuff(@s,1,10,'')
exec(@s)/*
id1 id2 id3 m val
---- ---- ---- ---- ----
a a a m1 am1
a a a m2 am2
a a a m3 am3
*/drop table aaa
我发现虽然可以定义 @s varchar(8000)
但当@s到4000后就自动截断了, 这样最多只能处理20列左右的数据, 不知道为什么, 请教