SQL2000环境中拆分字符串呈列值显示

需求介绍：

 

A表中

字段
  id productID buyNum
   
  1 （400,321,23,4） （1,2,1,2）
  2 （12,33,45） （2,2,2）
  3 （44,5332,33,45） （1,1,1,1）
  4 （332,313 ） （2,1）
  5 （32） （5）

字段 productID 和 buyNum 是一对一的，用逗号隔开，如何转换成列的形式

例如 字段  
  id productID buyNum
 
  1 400 1
  1 321 2
  1 23 1
  1 4 2
  2 12 2
  2 33 2
  2 45 2
  3 44 1
  3 5332 1
  3 33 1
  3 45 1

 

 

解决方法：（SQL2000）

 

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int, productID char(17),
buyNum char(12)
)
go
--插入测试数据
insert into tb select 1,'（400,321,23,4）','（1,2,1,2）'
union all select 2,'（12,33,45）','（2,2,2）'
union all select 3,'（44,5332,33,45）','（1,1,1,1）'
union all select 4,'（332,313）','（2,1）'
union all select 5,'（32）','（5）'
go

/*
思路：将 productID、buyNum 两个字段分成两部分来进行，分别取出字段值中逗号出现的位置，为进行 substring 截取字符
做准备！然后将这两部分按照预定的对应规则连接起来即可。

下面举例说明：
数据集 1：
id productID number idd
-------------------------------------
1    400,321,23,4    1    1    substring(productID+',',1,charindex(',',productID+',',1)-1)='400'
1    400,321,23,4    5    2    substring(productID+',',5,charindex(',',productID+',',5)-1)='321'
1    400,321,23,4    9    3    substring(productID+',',9,charindex(',',productID+',',9)-1)='23'
1    400,321,23,4    12    4    substring(productID+',',12,charindex(',',productID+',',12)-1)='4'
数据集 2：
id buyNum number idd
------------------------------
1    1,2,1,2    1    1           substring(productID+',',1,charindex(',',productID+',',1)-1)='1'
1    1,2,1,2    3    2           substring(productID+',',3,charindex(',',productID+',',3)-1)='2'
1    1,2,1,2    5    3           substring(productID+',',5,charindex(',',productID+',',5)-1)='1'
1    1,2,1,2    7    4           substring(productID+',',7,charindex(',',productID+',',7)-1)='2'

很显然数据集 1 和数据集 2 是上表中的第一行数据，其中 number 表示的是“,400,321,23,4”、“,1,2,1,2”中逗号出现的位置，
idd表示的是“,400,321,23,4”、“,1,2,1,2”中所取项的顺序位置，即：400(1),321(2),23(3),4(4)、1(1),2(2),1(3),2(4)
*/

--代码实现

select a.id,productID=substring(a.productID+',',a.number,charindex(',',a.productID+',',a.number)-a.number)
    ,buyNum=substring(b.buyNum+',',b.number,charindex(',',b.buyNum+',',b.number)-b.number)   --根据需求，按照提取出的number来截取以逗号分隔的字符串
from (
    select id,productID,number,idd=(select count(*) from (
            select * from (
                select id,productID=replace(replace(productID,'）',''),'（','')
                ,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
            )a   --去除干扰字符“（”、“）”
            inner join (
                select number from master..spt_values where type='p' and number!=0
            )b   --结合数据表 master..spt_values 生成自增编号
            on len(a.productID)+1>=b.number and substring(','+a.productID,b.number,1)=','   --按照字符串中的逗号，选择合适的、用来将字符串分段截取的起始值
        )aa where id=t.id and number<t.number)+1
    from (
        select * from (
            select id,productID=replace(replace(productID,'）',''),'（','')
            ,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
        )a   --去除干扰字符“（”、“）”
        inner join (
            select number from master..spt_values where type='p' and number!=0
        )b   --结合数据表 master..spt_values 生成自增编号
        on len(a.productID)+1>=b.number and substring(','+a.productID,b.number,1)=','   --按照字符串中的逗号，选择合适的、用来将字符串分段截取的起始值
    )t
)a /* a 中数据如下：

id productID number idd
-------------------------------------
1    400,321,23,4      1    1
1    400,321,23,4      5    2
1    400,321,23,4      9    3
1    400,321,23,4      12  4
2    12,33,45             1    1
2    12,33,45             4    2
2    12,33,45             7    3
3    44,5332,33,45    1    1
3    44,5332,33,45    4    2
3    44,5332,33,45    9    3
3    44,5332,33,45    12  4
4    332,313              1    1
4    332,313              5    2
5    32                       1    1
*/
inner join (
    select id,buyNum,number,idd=(select count(*) from (
            select * from (
                select id,productID=replace(replace(productID,'）',''),'（','')
                ,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
            )a   --去除干扰字符“（”、“）”
            inner join (
                select number from master..spt_values where type='p' and number!=0
            )b   --结合数据表 master..spt_values 生成自增编号
            on len(a.buyNum)+1>=b.number and substring(','+a.buyNum,b.number,1)=','   --按照字符串中的逗号，选择合适的、用来将字符串分段截取的起始值
        )aa where id=t.id and number<t.number)+1
    from (
        select * from (
            select id,productID=replace(replace(productID,'）',''),'（','')
            ,buyNum=replace(replace(buyNum,'）',''),'（','') from tb
        )a   --去除干扰字符“（”、“）”
        inner join (
            select number from master..spt_values where type='p' and number!=0
        )b   --结合数据表 master..spt_values 生成自增编号
        on len(a.buyNum)+1>=b.number and substring(','+a.buyNum,b.number,1)=','   --按照字符串中的逗号，选择合适的、用来将字符串分段截取的起始值
    )t
)b /* b 中数据如下：

id buyNum number idd
----------------------------------
1    1,2,1,2    1    1
1    1,2,1,2    3    2
1    1,2,1,2    5    3
1    1,2,1,2    7    4
2    2,2,2       1    1
2    2,2,2       3    2
2    2,2,2       5    3
3    1,1,1,1    1    1
3    1,1,1,1    3    2
3    1,1,1,1    5    3
3    1,1,1,1    7    4
4    2,1          1    1
4    2,1          3    2
5    5             1    1
*/
on a.id=b.id and a.idd=b.idd /* a、b 两者根据 id、idd 连接后的数据如下：

id productID number idd id buyNum number idd
------------------------------------------------------------------------
1    400,321,23,4      1    1    1    1,2,1,2    1    1
1    400,321,23,4      5    2    1    1,2,1,2    3    2
1    400,321,23,4      9    3    1    1,2,1,2    5    3
1    400,321,23,4      12  4    1    1,2,1,2    7    4
2    12,33,45             1    1    2    2,2,2       1    1
2    12,33,45             4    2    2    2,2,2       3    2
2    12,33,45             7    3    2    2,2,2       5    3
3    44,5332,33,45    1    1    3    1,1,1,1    1    1
3    44,5332,33,45    4    2    3    1,1,1,1    3    2
3    44,5332,33,45    9    3    3    1,1,1,1    5    3
3    44,5332,33,45    12  4    3    1,1,1,1    7    4
4    332,313              1    1    4    2,1          1    1
4    332,313              5    2    4    2,1          3    2
5    32                       1    1    5    5             1    1
*/

/*最终测试结果数据如下：

id productID buyNum
---------------------
1    400      1
1    321      2
1    23        1
1    4          2
2    12        2
2    33        2
2    45        2
3    44        1
3    5332    1
3    33        1
3    45        1
4    332      2
4    313      1
5    32        5

(14 行受影响)
*/