日期:2014-05-17 浏览次数:20418 次
CREATE TABLE 表1 (am varchar(10),ct varchar(10), mms int,ttl INT,
a12 char(2), a23 char(2), a38 char(2), a98 char(2))
INSERT 表1
SELECT 'sss001','r/t',223,1,'aa','aa','aa','aa' UNION ALL
SELECT 'sss002','t/n',232,2,'bb','bb','bb','bb' UNION ALL
SELECT 'sss003','t/n',2321,2,'ac','ac','ac','ac' UNION ALL
SELECT 'sss004','t/m',2323,2,'aa','aa','aa','aa'
declare @tsql varchar(max)
select @tsql='select am,ct,mms,ttl'
+(select ',left('+a.name+',1) '''+a.name+'-1'+''','
+'right('+a.name+',1) '''+a.name+'-2'+''' '
from sys.columns a
inner join sys.表1bles b on a.object_id=b.object_id
where b.name='表1' AND a.name NOT IN ('am','ct','mms','ttl') for xml path(''))
+' into 分开表 from 表1'
exec(@tsql)
SELECT * INTO 表2 from 分开表
select @tsql='select am,ct,mms,ttl'+
(SELECT ',['+a.name+']+['+(SELECT name FROM sys.columns WHERE LEFT(name,LEN(name)-2)=LEFT(a.name,LEN(a.name)-2) AND RIGHT(name,2)='-2')+'] ['+LEFT(a.name,LEN(a.name)-2)+']'
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
WHERE b.name='表2' AND RIGHT(a.name,2)='-1' for xml path(''))