字段分割（纵向）

Question

表01
ITEM NUM PATH
c 4 Root
f 3 Root+c
a 3 Root+c+f
m 2 Root+c+f+a
p 2 Root+c+f+a+m
b 1 Root+f
m 1 Root+c+f+a+b
b 1 Root+c+f+a
p 1 Root+c+b
f 1 Root
b 1 Root+c
表02
ITEM NUM PATH
c 4 Root
f 3 Root
f 3       c
a 3 Root
a 3       c
a 3       f
m 2 Root
m 2       c
m 2       f
m 2       a
p 2 Root
p 2       c
p 2       f
p 2       a
p 2       m
b 1 Root
b 1       f
m 1 Root
m 1       c
m 1       f
m 1       a
m 1       b
b 1 Root
b 1       c
b 1       f
b 1       a
p 1 Root
p 1       c
p 1       b
f 1 Root
b 1 Root
b 1       c想要把表01变成表02
即对表01中的PACH字段进行纵向拆分，对应的ITEM和NUM字段数据照抄。如表01中ITEM商品f，NUM购买人数为3人，其购买路径PATH为“Root+c”,对这个PATH进行拆分,变成两行，而其原先路径所对应的ITEM和NUM的数值不变，复制两行。

Answer 1

--参考
拆分表:--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-12-01 14:44:43
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
-- Apr 22 2011 11:57:00 
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ITEM] varchar(1),[NUM] int,[PATH] varchar(12))
insert [tb]
select 'c',4,'Root' union all
select 'f',3,'Root+c' union all
select 'a',3,'Root+c+f' union all
select 'm',2,'Root+c+f+a' union all
select 'p',2,'Root+c+f+a+m' union all
select 'b',1,'Root+f' union all
select 'm',1,'Root+c+f+a+b' union all
select 'b',1,'Root+c+f+a' union all
select 'p',1,'Root+c+b' union all
select 'f',1,'Root' union all
select 'b',1,'Root+c'
--------------开始查询--------------------------
Select
    a.ITEM,a.num,PATH=substring(a.PATH,b.number,charindex('+',a.PATH+'+',b.number)-b.number) 
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.PATH)
where
     substring('+'+a.PATH,b.number,1)='+'
----------------结果----------------------------
/* 
(11 行受影响)
ITEM num         PATH
---- ----------- ------------
c    4           Root
f    3           Root
f    3           c
a    3           Root
a    3           c
a    3           f
m    2           Root
m    2           c
m    2           f
m    2           a
p    2           Root
p    2           c
p    2           f
p    2           a
p    2           m
b    1           Root
b    1           f
m    1           Root
m    1           c
m    1           f
m    1           a
m    1           b
b    1           Root
b    1           c
b    1           f
b    1           a
p    1           Root
p    1           c
p    1           b
f    1           Root
b    1           Root
b    1           c(32 行受影响)
*/

Answer 3

create table table1(TID int,ITEM varchar(30))
insert into table1select * from table1select a.TID,
    Location=substring(a.ITEM,b.number,charindex('+',a.ITEM+'+',b.number)-b.number)
from table1 a,master..spt_values b
where b.[type] = 'p' and b.number between 1 and len(a.ITEM)
    and substring('+'+a.ITEM,b.number,1) = '+'/*
TID         ITEM
----------- ------------------------------
100         ROOT
100         ROOT+a
100         ROOT+a+c
100         ROOT+a+c+f
100         ROOT+a+c+f+m
200         ROOT
200         ROOT+a
200         ROOT+a+b
200         ROOT+a+b+f(9 行受影响)TID         Location
----------- ------------------------------
100         ROOT
100         ROOT
100         a
100         ROOT
100         a
100         c
100         ROOT
100         a
100         c
100         f
100         ROOT
100         a
100         c
100         f
100         m
200         ROOT
200         ROOT
200         a
200         ROOT
200         a
200         b
200         ROOT
200         a
200         b
200         f(25 行受影响)    

Answer 4

“master.dbo.spt_values”能解释一下这个表的作用吗？ 

Answer 5

构造一个数据段你查一下select number from master..spt_values where type='p' and number>=0 就知道了

Answer 6

if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 item varchar(10),
 num int,
 path varchar(20)
)
go
insert into tb
select 'c',4,'Root' union all
select 'f',3,'Root+c' union all
select 'a',3,'Root+c+f' union all
select 'm',2,'Root+c+f+a' union all
select 'p',2,'Root+c+f+a+m' union all
select 'b',1,'Root+f' union all
select 'm',1,'Root+c+f+a+b' union all
select 'b',1,'Root+c+f+a' union all
select 'p',1,'Root+c+b' union all
select 'f',1,'Root' union all
select 'b',1,'Root+c'
go
select item,num,substring(path,number,charindex('+',path+'+',number)-number) from tb a cross join master..spt_values b where b.type='p' and number between 1 and len(path) and substring('+'+path,number,1)='+'
go
/*
item       num         
---------- ----------- --------------------
c          4           Root
f          3           Root
f          3           c
a          3           Root
a          3           c
a          3           f
m          2           Root
m          2           c
m          2           f
m          2           a
p          2           Root
p          2           c
p          2           f
p          2           a
p          2           m
b          1           Root
b          1           f
m          1           Root
m          1           c
m          1           f
m          1           a
m          1           b
b          1           Root
b          1           c
b          1           f
b          1           a
p          1           Root
p          1           c
p          1           b
f          1           Root
b          1           Root
b          1           c(32 行受影响)
*/

Answer 7

要写一个FP_Tree数据挖掘算法，由于原始的交易型数据太过于庞大，需要进行压缩。所以首先需要进行数据的聚集。这样的话，由原先对交易数据的数据挖掘就变成了对压缩后的表（挖掘中的术语叫这个表为频繁树表）进行挖掘，所以这是就需要进行纵向的分割。这过程中的每一张表都不一样，都是最终实现算法的中间表，都是为了得到某个字段而存在的。

Answer 8

vfp9.0:
use 表
SCAN FOR '+' $ PATH
  SCATTER MVMVAR
  n = ALINES(ar, STRTRAN(PATH, '+', CHR(13)))
  FOR i=1 TO n
  m.PATH = ar[i]
  INSERT INTO 表 FROM MEMVAR
  NEXT
ENDSCAN

Answer 9

哇,你們這些高手太厲害了,我看都看不懂.請問如何學好sql.
我學了兩年了,沒什麼進展啊.

Answer 10

vfp9.0:
use 表
SCAN FOR '+' $ PATH&&表开始循环  
SCATTER MVMVAR&&字段复制到变量中
  n = ALINES(ar, STRTRAN(PATH, '+', CHR(13)))&&以+分割，用上回车替换，复制到数组
  FOR i=1 TO n&&循环，n为有几个回车+1
  m.PATH = ar[i]&&赋值到变量
  INSERT INTO 表 FROM MEMVAR&&插入到表
  NEXT&&for循环结束
ENDSCAN&&表循环结束你试试。

Answer 11

你这是在程序中实现，我要的是SQL脚本实现。