¹ØÓÚSQL SERVER Êý¾Ý²ð·Ö

if object_id('tb') is not null drop table tb
go
create table tb([A] varchar(10),[B] int)
insert tb
select 'bcd',1 union all
select 'bcde',2 union all
select 'cd',3
go
¸öÈË·ÖÎöÕâµÀÌâµÄ˼·ÊÇ£º
ͨ¹ýSubstring() À´Ñ­»··Ö¸î B ÁÐϵÄ×Ö·û´®¡£¡£
--ÏëµÃµ½µÄ½á¹û£º
/**
a    b          
---- -----------
b    1
c    1
d    1
b    2
c    2
d    2
e    2
c    3
d    3
£¨ËùÓ°ÏìµÄÐÐÊýΪ 9 ÐУ©
**/
£¨1£©£ºselect
substring(a.A,b.number,1) as a,a.b
from tb a,
master..spt_values b
where b.type='P' and b.number>0
and b.number<len(a.a)
/**
a    b          
---- -----------
b    1
c    1
d    1
b    2
c    2
d    2
e    2
c    3
d    3
£¨ËùÓ°ÏìµÄÐÐÊýΪ 9 ÐУ©
**/
£¨2£©
SELECT TOP 100 ID=IDENTITY(INT,1,1) INTO #NUM from SYS.SYSCOLUMNS A,SYS.SYSCOLUMNS B
SELECT
    A.b,[VALUE]=SUBSTRING(A.a,B.ID,1)
from
    #TB A,#NUM B
WHERE
    b.id <=len(a.a)
/*
b          VALUE
----------- -----
1          b
1          c
1          d
2          b
2          c
2          d
2          e
3          c
3          d