麻烦各位了 有原始表这样: table: aaa id1, 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其他的列都是不固定的,( 列的数目和名字都不固定), 请各位指点,谢谢了
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
*/