--建测试表: create table user_order(id varchar(10), product varchar(100),number varchar(100)) insert user_order select '1001','3|4|9|10|','1|1|2|4|' insert user_order select '1002','2|4|9|10|','10|1|20|14|' insert user_order select '1003','3|5|9|2|','11|21|22|34|' --先建一个连续的号码表从1至8000,也就是N>=product,number最大长度 select identity(int,1,1) as N into num_tab from sysobjectsselect identity(int,1,1) as tempid,id, substring('|'+product,N+1,charindex('|','|'+product,N+1)-N-1) as product into #temp1 from user_order,num_tab where substring('|'+product,N,8000) like '|_%' order by id,N select identity(int,1,1) as tempid,id, substring('|'+number,N+1,charindex('|','|'+number,N+1)-N-1) as number into #temp2 from user_order,num_tab where substring('|'+number,N,8000) like '|_%' order by id,N select a.id, cast(a.product as int) as product, cast(b.number as int) as number from #temp1 a,#temp2 b where a.tempid=b.tempid结果:id product number ---------- ----------- ----------- 1001 3 1 1001 4 1 1001 9 2 1001 10 4 1002 2 10 1002 4 1 1002 9 20 1002 10 14 1003 3 11 1003 5 21 1003 9 22 1003 2 34剩下的GROUP BY,我不用写了吧。
create table user_order(id varchar(10), product varchar(100),number varchar(100))
insert user_order select '1001','3|4|9|10|','1|1|2|4|'
insert user_order select '1002','2|4|9|10|','10|1|20|14|'
insert user_order select '1003','3|5|9|2|','11|21|22|34|'
--先建一个连续的号码表从1至8000,也就是N>=product,number最大长度
select identity(int,1,1) as N into num_tab from sysobjectsselect identity(int,1,1) as tempid,id,
substring('|'+product,N+1,charindex('|','|'+product,N+1)-N-1) as product
into #temp1
from user_order,num_tab
where substring('|'+product,N,8000) like '|_%'
order by id,N
select identity(int,1,1) as tempid,id,
substring('|'+number,N+1,charindex('|','|'+number,N+1)-N-1) as number
into #temp2
from user_order,num_tab
where substring('|'+number,N,8000) like '|_%'
order by id,N
select a.id,
cast(a.product as int) as product,
cast(b.number as int) as number
from #temp1 a,#temp2 b where a.tempid=b.tempid结果:id product number
---------- ----------- -----------
1001 3 1
1001 4 1
1001 9 2
1001 10 4
1002 2 10
1002 4 1
1002 9 20
1002 10 14
1003 3 11
1003 5 21
1003 9 22
1003 2 34剩下的GROUP BY,我不用写了吧。