再次求助，将一个表中的部分列分成两列和其逆过程的语句
表1
amctmmsttla12a23a38a98等几百列
sss001r/t2231aaaaaaaa
sss002t/n2322bbbbbbbb
sss003t/n23212acacacac
sss004t/m23232aaaaaaaa
表2
amctmmsttla12-1a12-2a23-1a23-2a38-1a38-2a98-1a98-2
sss001r/t2231aaaaaaaa
sss002t/n2322bbbbbbbb
sss003t/n23212acacacac
sss004t/m23232aaaaaaaa
请帮忙写两段语句：
1， 从第一个表生成第二个表，并自动保存为表名为“分开表”的表
2，从第二个表生成第一个表，并自动保存为表名为“合并表”的表
[解决办法]
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(''))
+' into 合并表 from 表2'
exec(@tsql)
create table 表1
(am varchar(10), ct varchar(10), mms varchar(10), ttl varchar(10),
a12 varchar(10), a23 varchar(10), a38 varchar(10), a98 varchar(10))
insert into 表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 '
+stuff((select ','+a.name
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
where b.name='表1' and patindex('%a[0-9]%',a.name)=0 for xml path('')),1,1,'')
+cast((select ',left('+a.name+',1) '''+a.name+'-1'','
+'right('+a.name+',1) '''+a.name+'-2'' '
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
where b.name='表1' and patindex('%a[0-9]%',a.name)>0 for xml path('')) as varchar(6000))
+' into 分开表 from 表1 '
exec(@tsql)
select * from 分开表
/*
am ct mms ttl a12-1 a12-2 a23-1 a23-2 a38-1 a38-2 a98-1 a98-2
---------- ---------- ---------- ---------- ----- ----- ----- ----- ----- ----- ----- -----
sss001 r/t 223 1 a a a a a a a a
sss002 t/n 232 2 b b b b b b b b
sss003 t/n 2321 2 a c a c a c a c
sss004 t/m 2323 2 a a a a a a a a
(4 row(s) affected)
*/
-- 产生合并表
declare @tsql varchar(max)
select @tsql='select '
+stuff((select ','+a.name
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
where b.name='分开表' and patindex('%a%-[0-9]%',a.name)=0 for xml path('')),1,1,'')
+cast((select ',['+a.name+']+['+stuff(a.name,5,1,'2')+'] '''+left(a.name,3)+''' '
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
where b.name='分开表' and patindex('%a%-[0-9]%',a.name)>0 and a.column_id%2=1
for xml path('')) as varchar(6000))
+'into 合并表 from 分开表 '
exec(@tsql)
select * from 合并表
/*
am ct mms ttl a12 a23 a38 a98
---------- ---------- ---------- ---------- ---- ---- ---- ----
sss001 r/t 223 1 aa aa aa aa
sss002 t/n 232 2 bb bb bb bb
sss003 t/n 2321 2 ac ac ac ac
sss004 t/m 2323 2 aa aa aa aa
(4 row(s) affected)
*/
