求助,将一包含两个字母的列自动分成两列,每个字母自动分到新列中

[black5]

原数据表：
列1 列2 列3 ...
AA GG GG
CC GG GG
CC AA CC
GG GG GG
AA AA AA
...

目标数据表：
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

谢谢！

[Andy__Huang]

最简单的做法:

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

/*
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 原数据表
(列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)
*/

[black5]

能不能写个循环?2000多列呢.谢谢!

[唐诗三百首]

引用 3 楼 black5 的回复:

能不能写个循环?2000多列呢.谢谢!

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)
*/