表结构转换

Question

表A
ID PID
----------
1  3,4,5,6,7,
2  4,5,6,7,
3  1,3,5,7,要得到如下结果：ID PID
--------
1  1
2  3
3  4
4  5
5  6
6  7不创建函数，不使用临时表，不使用游标SQL2000下请问该怎么写（ID是主键）？

Answer 1

if object_id('tb') is not null
   drop table tb
gocreate table tb(id int,pid varchar(50))
goinsert into tb select 1,'3,4,5,6,7,' union all
select 2,'4,5,6,7,' union all
select 3,'1,3,5,7,'go ;with liang as
(
    select distinct pid from
   (
select id,
   pid= substring(pid,number,charindex(',',pid,number)-number) 
from tb,master..spt_values a 
where a.type = 'P' 
  and substring(','+pid,number,1)=','  
  and number <len(pid)
   )T
)
select id =(row_number() over(order by pid)),pid from liangdrop table tb

Answer 2

表拆分，见精华帖
--> 1. CTE 递归找分隔字符位置法：速度极快
with T (id,P1,P2) as
(
    select id,charindex(',',','+name),charindex(',',name+',')+1 from #T
    union all
    select a.id,b.P2,charindex(',',name+',',b.P2)+1 from #T a join T b on a.id=b.id where charindex(',',name+',',b.P2)>0
)
select a.id,name=substring(a.name+',',b.P1,b.P2 - b.P1 - 1) from #T a join T b on a.id=b.id order by 1

Answer 3

http://topic.csdn.net/u/20070813/14/401e82ac-4770-4ae6-987f-034afee36e4c.html

Answer 4

我的blog上有讲解create table #tab1 (aID INT,b VARCHAR(10))
INSERT INTO #tab1 values(1,'1,2')
insert into #tab1 values(2,'2,3,5')
insert into #tab1 values(3,'6')
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b 
select * from #
/*
先看substring的条件当aID为1的时候那么b就为1,2
此时在看substring(',1,2',1,1)=','条件成立,那么此时b.id=1
在看CHARINDEX(',','1,2,',1)结果为2减去b.id的1,在看substring(1,'1,2',1)那就是1
单d.id为2的时候看substring(',1,2',2,1)=1而不是','所以不满足,在看3满足条件
*/
SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','http://blog.csdn.net/ws_hgo/archive/2009/04/13/4068286.aspx