求助,将一包含两个字母的列自动分成两列,每个字母自动分到新列中
原数据表：
列1 列2 列3 ...
AAGGGG
CCGGGG
CCAACC
GGGGGG
AAAAAA
...
目标数据表：
1 2 3 4 5 6
A A G G G G
C C G G G G
C C A A C C
G G G G G G
A A A A A A
谢谢！
[解决办法]
最简单的做法:
create table #tb(col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert into #tb
select 'AA','GG','GG'
union all select 'CC','GG','GG'
union all select 'CC','AA','CC'
union all select 'GG','GG','GG'
union all select 'AA','AA','AA'
select '1' as [1],'2' as [2],'3' as [3],'4' as [4],'5' as [5],'6' as [6]
union all
select substring(col1,1,1),substring(col1,2,1),
substring(col2,1,1),substring(col2,2,1),
substring(col3,1,1),substring(col3,2,1)
from #tb
/*
123456
AAGGGG
CCGGGG
CCAACC
GGGGGG
AAAAAA
*/
create table 原数据表
(列1 varchar(5), 列2 varchar(5), 列3 varchar(5))
insert into 原数据表
select 'AA', 'GG','GG' union all
select 'CC', 'GG', 'GG' union all
select 'CC', 'AA', 'CC' union all
select 'GG', 'GG', 'GG' union all
select 'AA', 'AA', 'AA'
select left(列1,1) [1],right(列1,1) [2],
left(列2,1) [3],right(列2,1) [4],
left(列3,1) [5],right(列3,1) [6]
from 原数据表
/*
1 2 3 4 5 6
---- ---- ---- ---- ---- ----
A A G G G G
C C G G G G
C C A A C C
G G G G G G
A A A A A A
(5 row(s) affected)
*/
create table 原数据表
(列1 varchar(5), 列2 varchar(5), 列3 varchar(5))
insert into 原数据表
select 'AA', 'GG', 'GG' union all
select 'CC', 'GG', 'GG' union all
select 'CC', 'AA', 'CC' union all
select 'GG', 'GG', 'GG' union all
select 'AA', 'AA', 'AA'
declare @tsql varchar(max)
select @tsql='select '
+stuff((select ',left('+a.name+',1) '''+rtrim(a.column_id*2-1)+''','
+'right('+a.name+',1) '''+rtrim(a.column_id*2)+''' '
from sys.columns a
inner join sys.tables b on a.object_id=b.object_id
where b.name='原数据表' for xml path('')),1,1,'')
+' from 原数据表'
exec(@tsql)
/*
1 2 3 4 5 6
---- ---- ---- ---- ---- ----
A A G G G G
C C G G G G
C C A A C C
G G G G G G
A A A A A A
(5 row(s) affected)
*/
